Often when building a database not all properties and settings are configured properly when initially created. One configuration property that we see left dangling the most is the default log file growth property of each database. The default allows a database log file to grow to a substantially large file size if not restricted. Often this file can leave your SQL Server box with low disk space errors and can crash SQL Server (prevent it from starting, etc.).

One quick and dirty way to alleviate a bloated log file’s size is to run a the command below in a new query window.

In the example below “GLReporting” is the name of the database in question and “GLReporting_log” is the alias name of the physical log file “GLReporting_Log.LDF”. Notice that the first parameter of the DBCC SHRINKFILE… command does not contain the “.LDF” file extension.

USE GLReporting
GO
DBCC SHRINKFILE(GLReporting_log, 200)
BACKUP LOG GLReporting WITH TRUNCATE_ONLY
DBCC SHRINKFILE(GLReporting_log, 200)
GO

Further Important Information
As a side note to the above quick and dirty script, the best practice would be to prevent the database log file from growing out of control in the first place. To do this you must configure the log file growth restriction property. To accomplish this run the SQL Server Management Studio application, connect to your DB server, and open the Object Explorer.

Expand your database folder list. Right-click on the database in question and select “Properties”.

In the left hand menu of the new prompt select “Files”. In right-hand side of the the window prompt find the log file in question (sort by the File Type column if it helps you). Under the “Authgrowth” column click on the elipse for the log file row in question.

Under the “Maximum File Size” section ensure that “Restricted File Growth(MB)” is selected. The default setting for this property is 2,097,152 MB which is roughly 2 TeraBytes if my math is right.

So, let’s change that value to something more managable like 2,152 which gives us a max size of 2GB. If that is too low for you data then bump it up to something like 10,152 (10GB). Most basic databases shouldn’t need more that 10GB log space ever. Click “OK” at all of the prompts and you are finished.

A while back I was working with a client on a web application project that had a basic SQL Server database as the relational data storage system. They wanted to use a rather “locked-down” SQL Authentication mode for the credentials that would allow the web app to communicate with the database. This user had read/write ability only. However, the web application would access the database mainly through use of stored procedure calls. As we all know, if a read/write user is going to communicate via stored procedures that user must have EXECUTE permissions on each stored procedure in question. We set this up in SQL Server security and the ASP.NET web.config file of the web application and everything worked well.

We finally were requested to move their “finally” tuned developed database to their production system. So, we did. Production worked great for weeks. Then the client needed a “quick” enhancement. So, during our maintenance window we created a backup of the production database and copied it down to development to begin the work. We dropped the existing development database and restored the backup of production on the development server. We started the web application and immediately encountered problems. Eventually, we noticed that the “login” field in the user security properties was blank. This is the login name that is required by the web.config credentials and it was missing. To keep a long story short we circumvented the issue by dropping and recreating the user so the login would again be function. That was a solution that worked but it was not the right one. After more research we came across an article on SQL Server Logins and Users. Basically the problem is that the master database stores the user login information for each database user. When the database is dropped, the user still exist in the server security model and has no home to belong to. The user is basically “orphaned”.

Here is a template script to solve the issue whenever it occurs:

Use Master
GO

--// Let's see the current global system ID for this login
SELECT sid FROM
dbo.syslogins
WHERE
name = [MyLoginName]

--// User your DB in question
USE
[TheDB]
GO

--// Let's see the current global system ID for this user
SELECT sid
FROM
dbo.sysusers
WHERE name = [MyLoginName]
GO

--// Maps an existing database user to a SQL Server login. The "report" call simply lists the users and their SIDs
sp_change_users_login 'report'
GO

--// Perform the re-link
sp_change_users_login 'update_one', [MyLoginName], [MyLoginName]

--// Confirm that we are now in sync
Use Master
GO
SELECT sid FROM
dbo.syslogins
WHERE
name = [MyLoginName]

SELECT sid
FROM
dbo.sysusers
WHERE name = [MyLoginName]