Just a day ago I have received following email from Siddhi and I found it interesting so I am sharing with all of you.
I have seen many blogs from you on SQL server and i have always found them useful and easy to understand. Thanks for all the information you provide.
I have one query about shrinking NDF and MDF files.
Can we shrink NDF and MDFfiles?? If you do so is there any data loss?
I have been shrinking the .LDF files every now and then but I am not too sure about NDF and MDF files.
Can you please answer my query.
Waiting for your early response.
Shrinking MDF and NDF file is possible and there is no chance of data loss.
It is not always advisable to shrink those file as those files are usually growing. There are cases when one database is separated in multiple database of any large table is dropped from database MDF and NDF can contain large empty space. This is the time they should be shrank. Shrinking database can be many hours process but it there are very slim chances of data lose.
Following is the script to shrink whole database.
DBCC SHRINKDATABASE (dbName)
Following is the script to shrink single file.
DBCC SHRINKFILE (logicalLogFileName)
To find logicalLogFileName following command has to be ran.
USE dbName EXEC sp_helpfile
Let us understand this using database AdventureWorks.
/* Shrink Whole AdventureWorks Database */ DBCC SHRINKDATABASE (AdventureWorks) GO /* Get the Logical File Name */ USE AdventureWorks EXEC sp_helpfile GO /* Shrink MDF File of AdventureWorks Database */ DBCC SHRINKFILE (AdventureWorks_Data) GO
Following image of the same process show when whole process is done there will be resultset with information about the new states of the database files.
I just came across very interesting article from SANS Institute. Experts from more than 30 US and international cyber security organizations have released list of 25 most dangerous programming errors and their resolution. It may be possible that many of the programmers may not understand what this errors are and how to implement their solution. As said this are 25 most dangerous errors and all the developers should atleast know what they are so they do not are prevented from origin. Here are four major advantages listed by SANS.
Software buyers will be able to buy much safer software.
Programmers will have tools that consistently measure the security of the software they are writing.
Colleges will be able to teach secure coding more confidently.
Employers will be able to ensure they have programmers who can write more secure code.
Following is the list of 25 most dangerous programming errors.
CATEGORY: Insecure Interaction Between Components
CWE-20: Improper Input Validation
CWE-116: Improper Encoding or Escaping of Output
CWE-89: Failure to Preserve SQL Query Structure (aka ‘SQL Injection’)
CWE-79: Failure to Preserve Web Page Structure (aka ‘Cross-site Scripting’)
CWE-78: Failure to Preserve OS Command Structure (aka ‘OS Command Injection’)
CWE-319: Cleartext Transmission of Sensitive Information
CWE-352: Cross-Site Request Forgery (CSRF)
CWE-362: Race Condition
CWE-209: Error Message Information Leak
CATEGORY: Risky Resource Management
CWE-119: Failure to Constrain Operations within the Bounds of a Memory Buffer
CWE-642: External Control of Critical State Data
CWE-73: External Control of File Name or Path
CWE-426: Untrusted Search Path
CWE-94: Failure to Control Generation of Code (aka ‘Code Injection’)
CWE-494: Download of Code Without Integrity Check
CWE-404: Improper Resource Shutdown or Release
CWE-665: Improper Initialization
CWE-682: Incorrect Calculation
CATEGORY: Porous Defenses
CWE-285: Improper Access Control (Authorization)
CWE-327: Use of a Broken or Risky Cryptographic Algorithm
CWE-259: Hard-Coded Password
CWE-732: Insecure Permission Assignment for Critical Resource
CWE-330: Use of Insufficiently Random Values
CWE-250: Execution with Unnecessary Privileges
CWE-602: Client-Side Enforcement of Server-Side Security
Please go to SANS site and click on each error to find their resolution.
If due to a long running query or any resource hogging query SQL Server is not responding sqlcmd can be used to connect to the server from another computer and kill the offending query. SQL Server provides a special diagnostic connection which allows administrators to access SQL Server when standard connection is not possible. Except very extreme situations dedicated administrator connection (DAC) is always successful.
Let us see how we can use DAC using sqlcmd.
Connect SQL Server using sqlcmd and -A option which will establish DAC.
C:\>sqlcmd -S localhost -U sa -P dev -d master -A
For our test purpose run following query which overload server and probably make it unresponsive. If your configure is not strong I strongly suggest not to run following query.
Following query is just for test purpose and not part of solution.
Once successfully connected it will provide prompt 1> enter following T-SQL query which will give SessionID of currently running query along with its elapsed time.
SELECT req.session_id, req.status, req.total_elapsed_time FROM sys.dm_exec_requests req WHERE status = 'running' AND req.total_elapsed_time > 1
Our previously running query gave session id 52 in on my server. The session id may be different for each SQL Server. Once the session id is figured out it can be killed using KILL [SessionID] command. Always make sure to type command GO after each complete query.
Once above query has run it will kill our example long running query and give following error.
Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.)
sqlcmd is really useful utility of SQL Server and it comes in handy when server is not responsive. I strongly suggest to bookmark this article as it can come to rescue when nothing works and SQL Server is unresponsive.
This is one very interesting issue which I have started to follow recently. Just like any other organization my company has many servers. Some are production and some are development. It is very much necessary that query which are written for developer environment does not run for production environment accidentally.
In SQL Server 2008 there is special feature which can change the color of the task bar. This will alert developer to run query on server.
Let us see quick tutorial with images which explains how the color of the status bar in SQL Server management studio can be changed.
I got following error when I was trying to delete user ‘test’ with ‘SA’ login. I was little surprised but then I tried to delete with the windows authenticated systemadmin account. Once again I got the same error.
Msg 15151, Level 16, State 1, Line 3
Cannot drop the login ‘test’, because it does not exist or you do not have permission.
The reason I was surprised that I was systemadmin and I should be allowed to delete the login. I am including the script which I used to delete the account here.
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'Test') DROP LOGIN [Test]
I finally found out that error was misleading. I had another session open in SQL Server Management Studio with username ‘test’ and it was preventing me to drop the user. Once I closed that open session I was successfully able to delete the user using above script.
In Normal Case:
Following error usually show up when user is logged in, which I did not get it. Msg 15434, Level 16, State 1, Line 3
Could not drop login ‘test’ as the user is currently logged in.
However, when I tried to re-create scenario I got above error. I want to share this experience with users and want to know if they have ever faced this scenario.
The reason I like blogging is follow up questions. I have wrote following two articles earlier this week. I just received question based on both of them. Before I go on questions, I recommend to read both of the article first. Both of them are very small article so they are quick to read.
The question I had received is following: “How to add any user to systemadmin role?“
This is very simple process.
Method 1: T-SQL
GO CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO EXEC master..sp_addsrvrolemember @loginame = N'test', @rolename = N'sysadmin' GO
Msg 15151, Level 16, State 1, Line 2
Cannot alter the login ‘sa’, because it does not exist or you do not have permission.
This error had occurred because of insufficient rights. Please read my previous post here before reading further article.
SA is system admin user and it is the highest level of user in system. If any user have to modify the permissions of SA that user needs to have higher or equivalent rights as SA user. Users member of systemadmin group are can only change the rights of SA user.
First adding any user to systemadmin role and then using the same user to modify system admin’s tool will fix the issue.