August 29, 2013 at 2:46 am
Help! I am confused. The following scenario is happening on our system.
I have a 2008 R2 SP2 two node cluster. The SQL Server service account is added to the "Perform volume Maintenance" local security policy option on both nodes.
After SQL installation, I always set the model database size to 64 MB data, 64 MB log and set the autogrow sizes for both to be 64 MB , just so that new databases have something a little better than the SQL default. This seems to be causing an issue though:
If I execute the following script:
CREATE DATABASE [test1] ON PRIMARY
( NAME = N'test1', FILENAME = N'E:\Data\test1.mdf' , SIZE = 1048MB , FILEGROWTH = 65536KB )
LOG ON
( NAME = N'test1_log', FILENAME = N'K:\Logs\test1.ldf' , SIZE = 64MB , FILEGROWTH = 65536KB )
The command takes 50 seconds to run.
If I set the model database to be a smaller initial size: 2 MB data, 17 MB log, the same command completes in just 3 seconds.
I am not sure why there is such a difference in time due to the size of the model database? I have never encountered this issue before.
Any help would be great.
Thanks.
August 29, 2013 at 4:34 am
No, that's pretty abnormal. I just did a test on one of my servers and I didn't see any difference at all in the time to create a database using the defaults or after changing the model database size. My suggestion would be to look at sys.dm_exec_requests to see what the process is waiting for. Is it blocked by some other process? Something along those lines, because simply changing the size of the model doesn't seem to automatically have those effects.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 29, 2013 at 6:13 am
Thanks for your reply. I can't fathom it either and cannot replicate it on any of my other servers, only this one.
I've just tested again and kept an eye on dm_exec_request. Nothing seems to be blocking the create database command. The wait type indicated is WRITE_COMPLETION which uses up the longest amount of time when the create database command is issued.
When I run the command with a smaller sized model database, the wait type for the create database command is ASYNC_IO_COMPLETION. I'm not quick enough to view any other stage of the db creation process!
August 29, 2013 at 6:29 am
Hmmm... This is 2008 right? How about putting extended events to work. You could try a few events like database_created, database_file_size_change, database_started, database_data_file_size_changed, file_written. It's not an investigation I've launched before, so those may not be the right events, but it's a start. On a guess, there's something up with the disk or disk sub-system on that server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 29, 2013 at 1:09 pm
is it taking time to format the log file?
August 30, 2013 at 2:22 am
Ok now I am more confused.
I just logged on the server to do some more testing and simple executed a "create database test" command with no other parameters. The model database is set as small as it can be, so in theory this should be a quick process. This command fails though, with the following error:
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'model'. Retry the operation later.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
So I look at what is blocking it and find when I execute the create db command there is a blocking process. This process is executing the following code:
DECLARE @edition sysname;
SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname);
select case when @edition = N'SQL Azure' then 1 else 0 end as 'IsCloud'
Then within a few seconds this blocking process is executing the following code:
select @@spid;
select SERVERPROPERTY('ProductLevel');
The strange thing is, the blocking process has a login which is my own login on the model database. It's not the same spid as the create database statement is executed under.
I cannot create a database on this server now due to this problem! Thankfully it is a test instance but I'm stumped as to what is going on!! 😀
SQL Version is:
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
I'm going to download the latest hotfix to see if this fixes any issues. All seems very strange!
August 30, 2013 at 3:19 am
Anti Virus maybe?
August 30, 2013 at 3:29 am
Nope. I've switched that off to make sure it's not interfering.
I restarted the SQL Server services a few times and this didn't solve the issue. I then failed the cluster over to the second node and low and behold this allowed the db to created fine. It could be something local to a particular node perhaps, so I'll continue testing and see if I can dig out any more issues!!
Strange one...
August 30, 2013 at 3:42 am
Has the service account got rights for Instant File Initialisation?
http://technet.microsoft.com/en-us/library/ms175935(v=sql.105).aspx
August 30, 2013 at 3:46 am
Yep. It is set on each node of the cluster.
August 30, 2013 at 3:48 am
you didnt have a query window open using the Model database (query running or not) did you? This would stop the database being created.
August 30, 2013 at 4:09 am
No, I thought that, so made sure all query windows were closed. All that was open was a query window using the master database and the activity monitor which I was using to monitor the blocking. No other connections where made on the model database at the time.
When the create database command was executed on the first query window (using sp 57), activity monitor showed a new session start (sp 58), which executed the queries I listed and blocked the sp 57 session. It then disconnected when the create db command failed.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply