CREATE TRIGGER [Security]
ON DATABASE
FOR ALTER_TABLE, ALTER_PROCEDURE, DROP_TABLE, DROP_PROCEDURE, ALTER_TRIGGER, DROP_TRIGGER
AS
IF SYSTEM_USER NOT IN ('YadavA', 'BhandariD','groverv','mittals')
BEGIN
RAISERROR ('You do not have necessary permissions to perform this action! Contact the DBA for further help',10, 1)
ROLLBACK
END
GO
Monday, September 27, 2010
Thursday, September 23, 2010
Move SQL Server transaction log files to a different location via T-SQL and SSMS
Example - T-SQL Commands
In this example, we will work through the steps to move a transaction log file to a new location via T-SQL commands. The first script will return the current locations, size, etc. of the database and transaction log files.
Script # 1: Capture database and transaction log file information
USE AdventureWorks
GO
sp_helpfile
GO
Below is sample output from the script showing that database has only two files. First is primary database file and second is the transaction log file. Although the file name, size, etc. may be different on your SQL Server, you will know the exact location of the files before the transaction log move begins. Note the location of the database file since it will be used during the database attach process.
Once you have the location information and have negotiated downtime with your users, now it is time to get exclusive access of the database in order to detach the database. If users are still connected to the database during the downtime, it is possible to remove them by using the With Rollback Immediate option or you can kill the connections via this tip.
Script # 2: Set database to single user mode and detach database
Use MASTER
GO
-- Set database to single user mode
ALTER DATABASE adventureWorks
SET SINGLE_USER
GO
-- Detach the database
sp_detach_db 'AdventureWorks'
GO
Now the database is detached. Once the detach process is completed, then you can copy and paste the new transaction log file then delete the old transaction log file via Windows Explorer. Once this is completed, we can attach the database with SQL Server database log file at new location with the following script:
Script # 3: Attach database with log file at new location
USE master
GO
-- Now Attach the database
sp_attach_DB 'AdventureWorks',
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\AdventureWorks_Data.mdf',
'E:\Move LogFile here through T-SQL\AdventureWorks_Log.ldf'
GO
After the final attach command our transaction log file has been moved to new location and our database is operational with log file on new location. Verifying the new database transaction log location can be accomplished by re-running script #1 above.
In this example, we will work through the steps to move a transaction log file to a new location via T-SQL commands. The first script will return the current locations, size, etc. of the database and transaction log files.
Script # 1: Capture database and transaction log file information
USE AdventureWorks
GO
sp_helpfile
GO
Below is sample output from the script showing that database has only two files. First is primary database file and second is the transaction log file. Although the file name, size, etc. may be different on your SQL Server, you will know the exact location of the files before the transaction log move begins. Note the location of the database file since it will be used during the database attach process.
Once you have the location information and have negotiated downtime with your users, now it is time to get exclusive access of the database in order to detach the database. If users are still connected to the database during the downtime, it is possible to remove them by using the With Rollback Immediate option or you can kill the connections via this tip.
Script # 2: Set database to single user mode and detach database
Use MASTER
GO
-- Set database to single user mode
ALTER DATABASE adventureWorks
SET SINGLE_USER
GO
-- Detach the database
sp_detach_db 'AdventureWorks'
GO
Now the database is detached. Once the detach process is completed, then you can copy and paste the new transaction log file then delete the old transaction log file via Windows Explorer. Once this is completed, we can attach the database with SQL Server database log file at new location with the following script:
Script # 3: Attach database with log file at new location
USE master
GO
-- Now Attach the database
sp_attach_DB 'AdventureWorks',
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\AdventureWorks_Data.mdf',
'E:\Move LogFile here through T-SQL\AdventureWorks_Log.ldf'
GO
After the final attach command our transaction log file has been moved to new location and our database is operational with log file on new location. Verifying the new database transaction log location can be accomplished by re-running script #1 above.
Key Questions for --- SQL Server Disaster Recovery Planning and Testing
Key Questions
The first step in testing your disaster recovery plan is to ask yourself and your team some poignant questions and respond in simple terms, maybe even 1 word answers. Depending on how you and your team answer these questions dictates how the fire drill can be conducted.
SLA - What is your Service Level Agreement (SLA) with the business or your customers?
Cost - What is the tangible cost of downtime for your users, application, business, etc?
Prevention - What type of disaster are you trying to prevent?
Recovery - What type of disaster are you trying to recover from?
Time - How much time and money do you have to spend on building, testing and maintaining the disaster recovery plan on a daily, weekly, monthly or quarterly basis?
Priority - Where does the disaster recovery plan fit into the organizational, departmental and personal priority list?
Need - What are the true disaster recovery risks for your organization based on where you are located, your line of business, your affiliations, etc?
Responsibility - What are your disaster recovery responsibilities and why do you have those responsibilities?
Plan - Do you have a documented disaster recovery plan?
Testing - Have you tested the disaster recovery plan?
Documentation - Do you update your documentation as dependent objects change? Or monthly? Or quarterly?
People - Will you have people to support the business?
Power - Will you have power to support the business?
Access - Will you be able to get to the office or make a remote connection?
Process - Will you have a process that everyone on the team can follow?
Technology - Have you invested in technology that will improve the prevention or recovery from a disaster?
Dependencies - Are you and your team dependent on other teams or external entities in order for your applications to operate properly? Do those teams test their plans on a regular basis?
Mitigation - Have you put multiple lines of defenses up to prevent a disaster as well as recover from one?
Limits - How long can your business run without the IT infrastructure and applications?
Alternatives - Can the business run on paper based operations for a finite period of time?
Experience - If you have an entire backup site\infrastructure have you failed over to it during a low usage period such as a Saturday or Sunday so the business really knows how the applications will perform even with a limited number of users?
Impacts - If you have an extended downtime, what will that do to your business in terms of customer loyalty, industry reputation, cash flow, etc?
The first step in testing your disaster recovery plan is to ask yourself and your team some poignant questions and respond in simple terms, maybe even 1 word answers. Depending on how you and your team answer these questions dictates how the fire drill can be conducted.
SLA - What is your Service Level Agreement (SLA) with the business or your customers?
Cost - What is the tangible cost of downtime for your users, application, business, etc?
Prevention - What type of disaster are you trying to prevent?
Recovery - What type of disaster are you trying to recover from?
Time - How much time and money do you have to spend on building, testing and maintaining the disaster recovery plan on a daily, weekly, monthly or quarterly basis?
Priority - Where does the disaster recovery plan fit into the organizational, departmental and personal priority list?
Need - What are the true disaster recovery risks for your organization based on where you are located, your line of business, your affiliations, etc?
Responsibility - What are your disaster recovery responsibilities and why do you have those responsibilities?
Plan - Do you have a documented disaster recovery plan?
Testing - Have you tested the disaster recovery plan?
Documentation - Do you update your documentation as dependent objects change? Or monthly? Or quarterly?
People - Will you have people to support the business?
Power - Will you have power to support the business?
Access - Will you be able to get to the office or make a remote connection?
Process - Will you have a process that everyone on the team can follow?
Technology - Have you invested in technology that will improve the prevention or recovery from a disaster?
Dependencies - Are you and your team dependent on other teams or external entities in order for your applications to operate properly? Do those teams test their plans on a regular basis?
Mitigation - Have you put multiple lines of defenses up to prevent a disaster as well as recover from one?
Limits - How long can your business run without the IT infrastructure and applications?
Alternatives - Can the business run on paper based operations for a finite period of time?
Experience - If you have an entire backup site\infrastructure have you failed over to it during a low usage period such as a Saturday or Sunday so the business really knows how the applications will perform even with a limited number of users?
Impacts - If you have an extended downtime, what will that do to your business in terms of customer loyalty, industry reputation, cash flow, etc?
SQL Server Monitoring Scripts with the DMVs
Problem
As a SQL Server DBA, dedicated to monitoring and administering many busy OLTP production environments, I always find myself trying to answer questions like: What was SQL Server doing last night when the end users were experiencing slow application response times? Was the issue from SQL Server? Or was it a network or a application issue? The answers to such questions are never easy to identify. If I only knew what SQL Server was doing at that specific point in time. If I knew what SQL Server was doing at that point in time, it may help me understand if the issues was a SQL Server performance issue or not . So how can I figure this out? If you do not have a third party monitoring tool in place, the only way is to constantly monitor SQL Server. To do this without being overly intrusive, we need to rely on the SQL Server DMVs.
Solution
This tip briefly describes how to successfully combine the usage of two SQL Server DMV’s (sys.dm_exec_requests and sys.dm_exec_sessions) to create a rudimentary, non intrusive and very efficient tool to monitor requests that are being executed against a SQL Server 2005 or SQL Server 2008 instance. The only two DMV’s that I will need to create my T-SQL based monitor script are sys.dm_exec_requests and sys.dm_exec_sessions. It is not my intention to explain the details of those two DMV's, I will only combine them to show what good information comes from them.
The following script comes in handy because it captures the code the SQL Server engine is processing at any point in time. Here is that query:
SELECT T.[text], P.[query_plan], S.[program_name], S.[host_name],
S.[client_interface_name], S.[login_name], R.*
FROM sys.dm_exec_requests R
INNER JOIN sys.dm_exec_sessions S
ON S.session_id = R.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS T
CROSS APPLY sys.dm_exec_query_plan(plan_handle) As P
GO
In order to create our simple monitoring tool, let's create the following monitoring table. Here is the script:
CREATE TABLE [dbo].[MyMonitorTable](
[text] [nvarchar](max) NULL,
[query_plan] [xml] NULL,
[host_name] [nvarchar](128) NULL,
[program_name] [nvarchar](128) NULL,
[client_interface_name] [nvarchar](32) NULL,
[login_name] [nvarchar](128) NOT NULL,
[session_id] [smallint] NOT NULL,
[request_id] [int] NOT NULL,
[start_time] [datetime] NOT NULL,
[status] [nvarchar](30) NOT NULL,
[command] [nvarchar](16) NOT NULL,
[sql_handle] [varbinary](64) NULL,
[statement_start_offset] [int] NULL,
[statement_end_offset] [int] NULL,
[plan_handle] [varbinary](64) NULL,
[database_id] [smallint] NOT NULL,
[user_id] [int] NOT NULL,
[connection_id] [uniqueidentifier] NULL,
[blocking_session_id] [smallint] NULL,
[wait_type] [nvarchar](60) NULL,
[wait_time] [int] NOT NULL,
[last_wait_type] [nvarchar](60) NOT NULL,
[wait_resource] [nvarchar](256) NOT NULL,
[open_transaction_count] [int] NOT NULL,
[open_resultset_count] [int] NOT NULL,
[transaction_id] [bigint] NOT NULL,
[context_info] [varbinary](128) NULL,
[percent_complete] [real] NOT NULL,
[estimated_completion_time] [bigint] NOT NULL,
[cpu_time] [int] NOT NULL,
[total_elapsed_time] [int] NOT NULL,
[scheduler_id] [int] NULL,
[task_address] [varbinary](8) NULL,
[reads] [bigint] NOT NULL,
[writes] [bigint] NOT NULL,
[logical_reads] [bigint] NOT NULL,
[text_size] [int] NOT NULL,
[language] [nvarchar](128) NULL,
[date_format] [nvarchar](3) NULL,
[date_first] [smallint] NOT NULL,
[quoted_identifier] [bit] NOT NULL,
[arithabort] [bit] NOT NULL,
[ansi_null_dflt_on] [bit] NOT NULL,
[ansi_defaults] [bit] NOT NULL,
[ansi_warnings] [bit] NOT NULL,
[ansi_padding] [bit] NOT NULL,
[ansi_nulls] [bit] NOT NULL,
[concat_null_yields_null] [bit] NOT NULL,
[transaction_isolation_level] [smallint] NOT NULL,
[lock_timeout] [int] NOT NULL,
[deadlock_priority] [int] NOT NULL,
[row_count] [bigint] NOT NULL,
[prev_error] [int] NOT NULL,
[nest_level] [int] NOT NULL,
[granted_query_memory] [int] NOT NULL,
[executing_managed_code] [bit] NOT NULL,
[group_id] [int] NOT NULL,
[query_hash] [binary](8) NULL,
[query_plan_hash] [binary](8) NULL
)
GO
In order to monitor the SQL Server activity in an automated manner, create a SQL Server Agent Job that executes the code below every minute.
INSERT INTO MyMonitorTable
SELECT T.text, P.query_plan, S.host_name, S.program_name, S.client_interface_name, S.login_name, R.*
FROM sys.dm_exec_requests R
JOIN sys.dm_exec_sessions S on S.session_id=R.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS T
CROSS APPLY sys.dm_exec_query_plan(plan_handle) As P
GO
After the job has been running for hours or days, we can query the MyMonitorTable table to see what SQL Server statements have been captured, as shown in the image below:
Script Caveats
It is important to clarify that this simple monitoring script does not capture all transactions like SQL Server Profiler does. This script with the SQL Server Agent Job only samples SQL Server transactions by the minute. You will not capture all queries. This script is aimed at capturing queries that are long running, may causing blocking and run frequently.
Here are also some columns to focus on as you begin to use the script:
For preliminary troubleshooting check out the following columns:
host_name
program_name
database_id
user_id
reads
writes
wait_type
wait_time
last_wait_type
wait_resource
The query_plan column shows (in xml format) the executed plan that a specific statement used. A graphical plan representation is also available if you click on the XML plan.
The blocking_session_id shows which session is currently blocking a statement and the wait type.
The statatement_text shows what query has been executed.
Statement_start_offset and Statement_start_offset can be used to list the exact T-SQL statement that was executed, within the batch job, at the specific point in time.
As a SQL Server DBA, dedicated to monitoring and administering many busy OLTP production environments, I always find myself trying to answer questions like: What was SQL Server doing last night when the end users were experiencing slow application response times? Was the issue from SQL Server? Or was it a network or a application issue? The answers to such questions are never easy to identify. If I only knew what SQL Server was doing at that specific point in time. If I knew what SQL Server was doing at that point in time, it may help me understand if the issues was a SQL Server performance issue or not . So how can I figure this out? If you do not have a third party monitoring tool in place, the only way is to constantly monitor SQL Server. To do this without being overly intrusive, we need to rely on the SQL Server DMVs.
Solution
This tip briefly describes how to successfully combine the usage of two SQL Server DMV’s (sys.dm_exec_requests and sys.dm_exec_sessions) to create a rudimentary, non intrusive and very efficient tool to monitor requests that are being executed against a SQL Server 2005 or SQL Server 2008 instance. The only two DMV’s that I will need to create my T-SQL based monitor script are sys.dm_exec_requests and sys.dm_exec_sessions. It is not my intention to explain the details of those two DMV's, I will only combine them to show what good information comes from them.
The following script comes in handy because it captures the code the SQL Server engine is processing at any point in time. Here is that query:
SELECT T.[text], P.[query_plan], S.[program_name], S.[host_name],
S.[client_interface_name], S.[login_name], R.*
FROM sys.dm_exec_requests R
INNER JOIN sys.dm_exec_sessions S
ON S.session_id = R.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS T
CROSS APPLY sys.dm_exec_query_plan(plan_handle) As P
GO
In order to create our simple monitoring tool, let's create the following monitoring table. Here is the script:
CREATE TABLE [dbo].[MyMonitorTable](
[text] [nvarchar](max) NULL,
[query_plan] [xml] NULL,
[host_name] [nvarchar](128) NULL,
[program_name] [nvarchar](128) NULL,
[client_interface_name] [nvarchar](32) NULL,
[login_name] [nvarchar](128) NOT NULL,
[session_id] [smallint] NOT NULL,
[request_id] [int] NOT NULL,
[start_time] [datetime] NOT NULL,
[status] [nvarchar](30) NOT NULL,
[command] [nvarchar](16) NOT NULL,
[sql_handle] [varbinary](64) NULL,
[statement_start_offset] [int] NULL,
[statement_end_offset] [int] NULL,
[plan_handle] [varbinary](64) NULL,
[database_id] [smallint] NOT NULL,
[user_id] [int] NOT NULL,
[connection_id] [uniqueidentifier] NULL,
[blocking_session_id] [smallint] NULL,
[wait_type] [nvarchar](60) NULL,
[wait_time] [int] NOT NULL,
[last_wait_type] [nvarchar](60) NOT NULL,
[wait_resource] [nvarchar](256) NOT NULL,
[open_transaction_count] [int] NOT NULL,
[open_resultset_count] [int] NOT NULL,
[transaction_id] [bigint] NOT NULL,
[context_info] [varbinary](128) NULL,
[percent_complete] [real] NOT NULL,
[estimated_completion_time] [bigint] NOT NULL,
[cpu_time] [int] NOT NULL,
[total_elapsed_time] [int] NOT NULL,
[scheduler_id] [int] NULL,
[task_address] [varbinary](8) NULL,
[reads] [bigint] NOT NULL,
[writes] [bigint] NOT NULL,
[logical_reads] [bigint] NOT NULL,
[text_size] [int] NOT NULL,
[language] [nvarchar](128) NULL,
[date_format] [nvarchar](3) NULL,
[date_first] [smallint] NOT NULL,
[quoted_identifier] [bit] NOT NULL,
[arithabort] [bit] NOT NULL,
[ansi_null_dflt_on] [bit] NOT NULL,
[ansi_defaults] [bit] NOT NULL,
[ansi_warnings] [bit] NOT NULL,
[ansi_padding] [bit] NOT NULL,
[ansi_nulls] [bit] NOT NULL,
[concat_null_yields_null] [bit] NOT NULL,
[transaction_isolation_level] [smallint] NOT NULL,
[lock_timeout] [int] NOT NULL,
[deadlock_priority] [int] NOT NULL,
[row_count] [bigint] NOT NULL,
[prev_error] [int] NOT NULL,
[nest_level] [int] NOT NULL,
[granted_query_memory] [int] NOT NULL,
[executing_managed_code] [bit] NOT NULL,
[group_id] [int] NOT NULL,
[query_hash] [binary](8) NULL,
[query_plan_hash] [binary](8) NULL
)
GO
In order to monitor the SQL Server activity in an automated manner, create a SQL Server Agent Job that executes the code below every minute.
INSERT INTO MyMonitorTable
SELECT T.text, P.query_plan, S.host_name, S.program_name, S.client_interface_name, S.login_name, R.*
FROM sys.dm_exec_requests R
JOIN sys.dm_exec_sessions S on S.session_id=R.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS T
CROSS APPLY sys.dm_exec_query_plan(plan_handle) As P
GO
After the job has been running for hours or days, we can query the MyMonitorTable table to see what SQL Server statements have been captured, as shown in the image below:
Script Caveats
It is important to clarify that this simple monitoring script does not capture all transactions like SQL Server Profiler does. This script with the SQL Server Agent Job only samples SQL Server transactions by the minute. You will not capture all queries. This script is aimed at capturing queries that are long running, may causing blocking and run frequently.
Here are also some columns to focus on as you begin to use the script:
For preliminary troubleshooting check out the following columns:
host_name
program_name
database_id
user_id
reads
writes
wait_type
wait_time
last_wait_type
wait_resource
The query_plan column shows (in xml format) the executed plan that a specific statement used. A graphical plan representation is also available if you click on the XML plan.
The blocking_session_id shows which session is currently blocking a statement and the wait type.
The statatement_text shows what query has been executed.
Statement_start_offset and Statement_start_offset can be used to list the exact T-SQL statement that was executed, within the batch job, at the specific point in time.
SQL Server Transaction Log Grows And Fills Up Drive
Problem
I had a production problem recently, the transaction log grew 1 GB every minute and filled up the entire drive. The error message I got was: "The transaction log for database 'MyDB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases. My SQL Server is clustered and the issue happened after a node failover occurred. In this tip I cover steps that I took to resolve this problem.
Solution
The message I got was the transaction log drive is full and users are unable to access the application, DBAs never want to hear this! The usual DBA question is "what was changed"?
My first priority was to fix the problem immediately, so users can access the application and then do root cause analysis. For the database in question, I changed the database recovery model to SIMPLE. Then I shrank the transaction log file to a reasonable size (note: you can't shrink it below its original size). After these changes, the log growth stopped, but this still did not tell me what the issue was. Something to note is that changing the recovery model to SIMPLE is not possible if Mirroring or Log Shipping is setup unless you remove these first, but these can be reconfigured later, the important thing is to stop the bleeding ASAP.
Other options would have been to create another transaction log file on a different drive that had plenty of space or to clean up files on the drive that was running out of space. But in my case the file was growing so rapidly that I needed to change the recovery model so the growth would stop until this issue was resolved.
Below are the steps that I took to check the size, change the recovery model and shrink the log file.
SELECT recovery_model_desc FROM sys.databases WHERE name = 'MyDB' --Before
ALTER DATABASE MyDB SET recovery simple
SELECT recovery_model_desc FROM sys.databases WHERE name = 'MyDB' --After
EXEC xp_fixeddrives --Check free drive space
EXEC sp_helpdb MyDB -- Note the size of the log before shrink
DBCC shrinkfile(MyDB_log, 1024) -- shrink log to 1 GB
EXEC sp_helpdb MyDB -- Note the size of the log after shrink
EXEC xp_fixeddrives -- Check free drive space
First aid is complete, database is up and users are able to use the application again.
Now I need to find out what was causing the log to grow so fast, fix the issue, put the database back in FULL recovery mode, do a full backup and make sure my log backups are working correctly.
The drive now has enough space. I put the database in Full recovery mode again to see if I could catch the offending transaction and I monitored drive space and transaction log size. I immediately noticed the transaction log growing about 1 GB per minute. I then executed the block of code below to find out what may be causing the log growth.
DBCC opentran --get the spid and feed it into the next query
DECLARE @handle BINARY(20)
DECLARE @SPID INT
SET @SPID = 100 -- 100 was the spid from the dbcc opentran
SELECT @handle = sql_handle
FROM MASTER..sysprocesses
WHERE spid = @SPID
SELECT [text] FROM ::fn_get_sql(@handle)
GO
The culprit was a stored procedure that was showing up every time I executed the above block of code, although the SPID changed each time. I looked into the stored procedure which was not very complicated, but does a bunch of deletes and inserts based on some condition. This procedure was called by an automated process every minute. What happens when a cluster node fail-over happens? The cache is flushed and all the query plans are gone. Since there has not been any issue with this procedure in the past, my first option was to recompile the procedure. Viola, it worked! The transaction log stopped growing so fast and I didn't see this procedure in the open transactions from the above code. So this simple stored procedure that worked fine suddenly caused a major production issue causing the application to shut down and the fix was as simple as a recompile. Quite interesting.
My next step was to start a full database backup ASAP. While the full database backup was running I kicked off transaction log backups every 5 minutes and they seemed to work fine. I changed the transaction log backup to its normal schedule. After the full backup was completed, I reviewed the sql log, drive space, transaction log size, open transactions and everything seemed fine. If it is a cluster, you could try failing back to the other node and see if that fixes the problem, which in my case was the need for a recompile. In a cluster, the storage is shared by the nodes, so there is no benefit from a storage point of view.
I had a production problem recently, the transaction log grew 1 GB every minute and filled up the entire drive. The error message I got was: "The transaction log for database 'MyDB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases. My SQL Server is clustered and the issue happened after a node failover occurred. In this tip I cover steps that I took to resolve this problem.
Solution
The message I got was the transaction log drive is full and users are unable to access the application, DBAs never want to hear this! The usual DBA question is "what was changed"?
My first priority was to fix the problem immediately, so users can access the application and then do root cause analysis. For the database in question, I changed the database recovery model to SIMPLE. Then I shrank the transaction log file to a reasonable size (note: you can't shrink it below its original size). After these changes, the log growth stopped, but this still did not tell me what the issue was. Something to note is that changing the recovery model to SIMPLE is not possible if Mirroring or Log Shipping is setup unless you remove these first, but these can be reconfigured later, the important thing is to stop the bleeding ASAP.
Other options would have been to create another transaction log file on a different drive that had plenty of space or to clean up files on the drive that was running out of space. But in my case the file was growing so rapidly that I needed to change the recovery model so the growth would stop until this issue was resolved.
Below are the steps that I took to check the size, change the recovery model and shrink the log file.
SELECT recovery_model_desc FROM sys.databases WHERE name = 'MyDB' --Before
ALTER DATABASE MyDB SET recovery simple
SELECT recovery_model_desc FROM sys.databases WHERE name = 'MyDB' --After
EXEC xp_fixeddrives --Check free drive space
EXEC sp_helpdb MyDB -- Note the size of the log before shrink
DBCC shrinkfile(MyDB_log, 1024) -- shrink log to 1 GB
EXEC sp_helpdb MyDB -- Note the size of the log after shrink
EXEC xp_fixeddrives -- Check free drive space
First aid is complete, database is up and users are able to use the application again.
Now I need to find out what was causing the log to grow so fast, fix the issue, put the database back in FULL recovery mode, do a full backup and make sure my log backups are working correctly.
The drive now has enough space. I put the database in Full recovery mode again to see if I could catch the offending transaction and I monitored drive space and transaction log size. I immediately noticed the transaction log growing about 1 GB per minute. I then executed the block of code below to find out what may be causing the log growth.
DBCC opentran --get the spid and feed it into the next query
DECLARE @handle BINARY(20)
DECLARE @SPID INT
SET @SPID = 100 -- 100 was the spid from the dbcc opentran
SELECT @handle = sql_handle
FROM MASTER..sysprocesses
WHERE spid = @SPID
SELECT [text] FROM ::fn_get_sql(@handle)
GO
The culprit was a stored procedure that was showing up every time I executed the above block of code, although the SPID changed each time. I looked into the stored procedure which was not very complicated, but does a bunch of deletes and inserts based on some condition. This procedure was called by an automated process every minute. What happens when a cluster node fail-over happens? The cache is flushed and all the query plans are gone. Since there has not been any issue with this procedure in the past, my first option was to recompile the procedure. Viola, it worked! The transaction log stopped growing so fast and I didn't see this procedure in the open transactions from the above code. So this simple stored procedure that worked fine suddenly caused a major production issue causing the application to shut down and the fix was as simple as a recompile. Quite interesting.
My next step was to start a full database backup ASAP. While the full database backup was running I kicked off transaction log backups every 5 minutes and they seemed to work fine. I changed the transaction log backup to its normal schedule. After the full backup was completed, I reviewed the sql log, drive space, transaction log size, open transactions and everything seemed fine. If it is a cluster, you could try failing back to the other node and see if that fixes the problem, which in my case was the need for a recompile. In a cluster, the storage is shared by the nodes, so there is no benefit from a storage point of view.
Subscribe to:
Posts (Atom)