Looking into Transaction Log size and clearing and database backups January 4, 2013
Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.Tags: database backups, recovery models, transaction log
1 comment so far
In this post I would like to talk about the size of the transaction log and under what circumstances it clears in relation to recovery models and database backup types (Full, Differential, Log). I will also give a brief overview of recovery models, database backup types.
When I usually write a blog post that is often due to questions I receive in my mailbox or real problems my clients face and I have to resolve. A few days ago I got an email from a friend of mine. He was telling me that his website was down and was not functioning. He said the error he was seeing in 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”
It is obvious that there is a problem with the transaction log and that is why his site is not functioning.I will also provide hands-on examples when needed.
Before we proceed I would like to explain the various recovery models.We have 3 recovery models available in SQL Server
- Simple
- Full
- Bulk-Logged
In the Simple Recovery Model every time I have a checkpoint operation or the .ldf file is full, the .ldf file is automatically truncated.In this model the transaction log is not backed up.You can not have point in time data recovery with this model.Data is recoverable only to the most recent backup.
The Simple Recovery Model is only suitable for small databases and databases with few transactions.We could use this recovery model if we want to have a small transaction log and for databases that are read-only or in development stage.
The Full Recovery Model is the default recovery model.In this model the transaction log grows rapidly since all database transactions are logged in at a very detailed level.If I have a catastrophe in my database I can rollback to the time where the catastrophe occurred.This recovery model is suitable for databases with thousands of transactions per day and where no loss of data is tolerated.
The Bulk Recovery Model works almost identically with the Full Recovery Model.It logs everything in extents=8pages and all bulk tasks (bcp,bulk insert) are logged minimally.We can go back to the time of failure but it will take more time to get the database back on-line.This model would be very suitable for databases with large bulk tasks.The disadvantage of this model is that you cannot go back at a specific point in time.
Having looked into the recovery models,let’s have a look on the various SQL Server backup types.
Let’s see what happens when we take a full database backup.The first thing to say is that you need always a full database backup.This is the base for all subsequent backups. You cannot have a differential backup,transaction log backup if you do not have at least one full backup.With a full database backup you backup all your data.You can have a full backup with all the recovery models that you choose to have for your database.Every full backup I perform in a database contains in it all the data previously fully backed up.
The backup operation is an online operation and the first thing it happens is to log that a backup was started. Then we write the data in the backup file.Then we do log any changes that occur as we backup.Finally the backing up of the data is finished and we log that fact.If you’re performing only full backups, you might lose some data in the event of a system crash—specifically, any changes made since the last full backup.
A differential backup is cumulative backup. It includes all data and structures that have changed since the last full backup, regardless of when that last full backup was made, or how many previous differential backups have been run.You can have a differential backup with all the recovery models.
The Transaction Log backup only works for full or bulk-logged recovery models.Each T-Log backup contains all the transactions that are in my database since the last T-Log backup.Always perform a full back up before you take a T-log backup.The time taken for a T-log backup is little and there is no performance penalty.An important thing to remember is that for every t-log backup I need the previous backup to have a restore.
Let me come back to the original problem which is that my friend’s website run out of transaction log space.
This happens when the transaction log fills up to the point where it has to grow but either autogrow is not turned on or the volume on which the transaction log is placed has no more space for the file to grow.
That is profound. But what has caused the transaction log to fill up in the first place? It could be a number of different things.
The two most common ones I’ve seen are:
- The database is in full recovery model and subsequent full database backups were taken but no log backups have been taken.
This commonly happens when an application goes into production and someone decides to take a backup, without realizing the consequences. Log truncation is not automatic in such a case – a log backup is required to allow the log to truncate.
2. The database is in any recovery mode but there’s a very long-running, uncommitted transaction that prevents log truncation.
In this case I was called to examine, the problem was that the database was in full recovery mode, subsequent full database backups were taken but no log backups have been taken.
Some people believe that a full database backup in the full or bulk logged will clear/truncate the transaction log.
Well, these people are wrong. Simple as that. They are wrong.This is not true. Only a transaction log backup will clear the log under these recovery models.
On the other hand, in the SIMPLE recovery model, a checkpoint clears the transaction log. I see people that have critical data in their databases and need to restore it (in case of a catastrophe) to the point of failure, to use Simple recovery model in their databases because the transaction log is getting too large. So in order to keep it small, they switch recovery models. This is so wrong!!!! In case of a failure these people will lose data they cannot afford to lose.
Let me go on with the actual example
I will be using SQL Server 2012 Εnterprise edition in this post but feel free to use SQL Server 2008/R2 versions and any other edition you might have installed (Standard, Express e.t.c).
1) Launch SSMS and connect to the local (or any other instance) of SQL Server
2) I will be looking at the column log_reuse_wait_desc of the sys.databases
3) In a new query window in SSMS , type (or copy paste) the following t-sql script and then execute it (substitute database football with any other you have attached in the instance of SQL Server.)
SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'football'
In my case I receive “Nothing” and that is why you will receive yourself if you execute the T-SQL script above but in my friend’s case when I connected to the SQL hosting the database I go the following:
Log Backup
That is all I needed to know. SQL Server tells me what the problem is.The database was in Full recovery model, only full backups were taken and no log backups have been taken.
4) Now I will show you why a full database backup does not clear the transaction log
Type (copy and paste) the following T-SQL script but do not execute it yet.
USE MASTER;
GO
CREATE DATABASE [Football] ON PRIMARY
( NAME = N'Football_Data', FILENAME = N'D:\DATA\Football_Data.mdf' , SIZE = 80MB , MAXSIZE = UNLIMITED, FILEGROWTH = 20MB )
LOG ON
( NAME = N'Football_Log', FILENAME = N'D:\DATA\Football_log.ldf' , SIZE = 20MB , MAXSIZE = UNLIMITED, FILEGROWTH = 5MB)
GO
USE FOOTBALL;
GO
CREATE TABLE Footballer
(
FootballerID int identity(1,1),
Footballer_No varchar(20),
Footballer_lastname nvarchar(50),
Footballer_firstname nvarchar(50),
Footballer_middlename nvarchar(50)
)
GO
CREATE UNIQUE CLUSTERED INDEX cx_Foo_IX ON Footballer (FootballerID)
GO
declare @CTR INT
declare @ctrstr varchar(7)
select @ctr=0
WHILE @CTR<4000
BEGIN
select @ctr=@ctr+1
select @ctrstr=Str(@ctr)
insert footballer(Footballer_No ,Footballer_lastname,Footballer_firstname, Footballer_middlename) values('F_No'+@ctrstr,'Footlname'+@ctrstr,'Footfname'+@ctrstr,'Footmname'+@ctrstr)
END
BACKUP DATABASE [Football] TO DISK = N'D:\DATA\backup\football_full.bak' WITH INIT,STATS;
GO
BACKUP LOG [Football] TO DISK = N'D:\DATA\backup\football_log.bak' WITH INIT,STATS;
GO
ALTER INDEX cx_Foo_IX ON Footballer REBUILD;
GO
BACKUP LOG [Football] TO DISK = N'D:\DATA\backup\football1_log.bak' WITH INIT,STATS;
GO
ALTER INDEX cx_Foo_IX ON Footballer REBUILD;
GO
BACKUP DATABASE [Football] TO DISK = N'D:\DATA\backup\football1_full.bak' WITH INIT,STATS;
GO
BACKUP LOG [Football] TO DISK = N'D:\DATA\backup\football2_log.bak' WITH INIT,STATS;
GO
Let me explain what I am doing.
In lines 1-8 I create a sample database Football.
In lines 10-24, I create a table, Footballer and a unique clustered index on column FootballerID.
In lines 26-34 I populate the Footballer table.
In lines 36-37 I take full database backup.
In lines 39-40 I take log database backup.
In lines 42-43 I rebuild the clustered index.
In lines 46-47 I take another log backup.
In lines 49-50 I rebuild the clustered index.
In lines 52-53 I take another full database backup. If a database backup clears the log, the next transaction log , should be small.
In lines 55-56 I take another log backup.
Let’s now compare the size of the last two transaction logs. Have a look at the picture below
As you can see the size of the transaction logs is more or less the same.So the last transactional log is not smaller,thus the full database backup did not clear the log.
The way I solved my friend’s problem was to free disk space so that the log can automatically grow and then take a full backup following a log backup.
Then I explained him the importance of log backups in log truncation and size management. He has incorporated log backups in his database backup strategy.
Hope it helps!!!!
Looking into the DBCC LOGINFO and DBCC SQLPERF commands December 28, 2012
Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.Tags: dbcc commands, DBCC LOGINFO, DBCC SQLPERF
1 comment so far
In this post I would like to talk a bit about Transaction Log,its various parts and its architecture. I will not go into details about what Transaction Log is because it is well documented elsewhere. I would like also to highlight a few important points. Ι will provide hands-on examples for the DBCC LOGINFO and DBCC SQLPERF commands.
I have been helping out a friend to understand various important issues regarding Transaction Logs in SQL Server databases.The transaction log records all the transactions and the modifications on the database those transactions have. Usually you have one transaction log file per database but you can have multiple files.The default size of a transaction log (if not specified) is the 25% of the total size of all data files created for the database.It is always a good idea to have the size of the transaction log planned beforehand.Bear in mind that the transaction log is zero initialized and that is a costly operation in both time and resources.The transaction log is divided into smaller parts that are called VLFs (Virtual Log Files). It is easier to manage the transaction log like that. You have a active and inactive VLFs . One VLF must be active at any time in the database. You cannot configure the number and the size of the VLFs in the transaction log. As transaction log grows, new VLFs are created and their size is determined by SQL Server. There is a formula that determines the number and the size of the VLFs. If the transaction log grows by 64 MBytes, 4 new VLFs are created each 1/4 of the growth size.If the transaction log grows by 64 MBytes to 1Gbytes, 8 new VLFs are created each 1/8 of the growth size.If the transaction log grows by 1Gbytes or more, 16 new VLFs are created each 1/16 of the growth size.Also bear in mind that each VLF is uniquely identified by a sequence number.
Let’s have a look at the actual hands-on demos.
I will be using SQL Server 2012 Εnterprise edition in this post but feel free to use SQL Server 2008/R2 versions and any other edition you might have installed (Standard, Express e.t.c).
1) Launch SSMS and connect to the local (or any other instance) of SQL Server
2) I will be looking firstly at the DBCC LOGINFO command which provides us with important information about the structure of the transaction log.
3) In a new query window in SSMS , type (or copy paste) the following t-sql script and then execute it
USE MASTER; GO CREATE DATABASE [Football] ON PRIMARY ( NAME = N'Football_Data', FILENAME = N'D:\DATA\Football_Data.mdf' , SIZE = 180MB , MAXSIZE = UNLIMITED, FILEGROWTH = 20MB ) LOG ON ( NAME = N'Football_Log', FILENAME = N'D:\DATA\Football_log.ldf' , SIZE = 40MB , MAXSIZE = UNLIMITED, FILEGROWTH = 20MB) GO DBCC LOGINFO(N'Football'); GO
Let me explain what I do in this example. I create a database Football.Then I run the DBCC LOGINFO(N’Football’) to understand a bit about the log structure and the number of VLFs
Have a look at the photo below
As you can see there are 4 VLFs.Each one of them is 10 Mbytes.So we can see that the formula applies in this case. The Log file size is 40 Mbytes so we expect to have 4 VLFs.
4) Now let’s have a look at the DBCC SQLPERF(LOGSPACE) command. This command provides transaction log space usage statistics for all databases. Have a look
In a new query window type and execute the following T-Sql command.
USE MASTER; GO DBCC SQLPERF(LOGSPACE); GO
Have a look at the picture below
5) Now I am going to increase the size of the transaction log by inserting sample data in the database and then check again the number and the size of the VLFs in the transaction log. I will also check the transaction log file size and the part of it that is used.
Type (copy and paste) the following T-SQL script. Execute it
USE FOOTBALL; GO ALTER DATABASE Football MODIFY FILE (NAME =N'Football_Log',SIZE=60MB); DBCC LOGINFO(N'Football'); GO DBCC SQLPERF(LOGSPACE); GO
6) We add more space in the transaction log and then execute the DBCC LOGINFO and DBCC SQLPERF commands. Have a look at the picture below
We can see now that the size of the transaction log is more than 64Mbytes we have 8 VLFs
7) Now in a new query window type (copy paste) and execute the following script.
USE FOOTBALL;
GO
CREATE TABLE Footballer
(
FootballerID int identity(1,1),
Footballer_No varchar(20),
Footballer_lastname nvarchar(50),
Footballer_firstname nvarchar(50),
Footballer_middlename nvarchar(50)
)
GO
CREATE UNIQUE CLUSTERED INDEX cx_Foo_IX ON Footballer (FootballerID)
GO
declare @CTR INT
declare @ctrstr varchar(7)
select @ctr=0
WHILE @CTR<100.000
BEGIN
select @ctr=@ctr+1
select @ctrstr=Str(@ctr)
insert footballer(Footballer_No ,Footballer_lastname,Footballer_firstname, Footballer_middlename) values('F_No'+@ctrstr,'Footlname'+@ctrstr,'Footfname'+@ctrstr,'Footmname'+@ctrstr)
END
SELECT * FROM Footballer;
GO
I create a table in the database.Then I create a clustered index and insert 100.000 rows in it. That will increase the size of the used space in the transaction log.
Type and execute the T-SQL code below.
DBCC LOGINFO(N'Football'); GO DBCC SQLPERF(LOGSPACE); GO
Have a look at the picture below to see results I have got when I run the T-SQL code above.
Hope you learned about transaction log architecture and the importance of DBCC LOGINFO and DBCC SQLPERF commands.
Hope it helps!!!!
Configuring SQL Server after installation October 28, 2012
Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.Tags: SQL Server Configuration Manager
add a comment
In this post I will demonstrate how to configure SQL Server after installation. I have a relevant post in this blog on how to install SQL Server.
I have installed SQL Server Developer edition in my machine. Developer edition of SQL Server 2012 can be installed in a client operating system.
1) I will use SQL Server Configuration Manager to change some initial configurations.We can manage the various services that are running and are related to SQL Server.We can manage the various network protocols.
Have a look at the picture below to see how you can open SQL Server Configuration Manager.
2) After firing up this tool, I can see all the SQL Server Services that are installed in this machine.You can see their names, their state, their start mode, that security account they are running under,the Process ID and the Service Type.You can see that there are 3 Database SQL Server Services running in my machine.One service for the named instance for the SQL Server 2008 R2 Enterprise Edition, one named instance for SQL Server 2012 Express Edition and one service for the default instance of SQL Server 2012 Developer Edition.
Have a look at the picture below
3) We can Start, Stop, Pause, Resume, Restart each service. Have a look at the picture below
4) We can also set/change the various properties by clicking on Properties.
We can change the Start Mode of a particular service e.g From Automatic to Manual.Have a look at the picture below.
5) We can also configure settings that are relevant with network protocols.These are going to be the protocols that SQL Server listens to for client connections.
When I select the protocols for the MSSQLSERVER I see there are 3 protocols:Shared Memory,Named Pipes,TCP/IP.If you want to allow remote connections from clients over a network like the Internet that supports TCP/IP we must enable that protocol.
Have a look at the picture below
We can also change some settings regarding the TCP/IP protocol by clicking on Properties.We can change the default TCP/IP port number 1433, to something else.
After changing these settings you may need to restart the service.
Have a look at the picture below
6) Another thing you can do with SQL Server Configuration Manager is configuring client connections to other SQL Servers, meaning how this SQL Server instance is going to make outbound database connections.
Have a look at the picture below.Please take a note on the order of the protocols.You can see that all the protocols are enabled.
These are the protocols that SQL Server uses to connect to other instances of SQL Server. We can also set aliases from SQL Server Configuration Manager.A client will connect to the alias ( a name other the server instance name ).You can set the alias name, the port number,the protocol and the server it will connect to.
Have a look at the picture below
7) In order to show you the rest of SQL Server configurations I will switch to the familiar environment of SQL Server Management Studio – SSMS.Make sure you close the SQL Server Configuration Manager snap-in.
I will connect through SSMS to my SQL Server 2012 Developer Edition using Windows Authentication.Then I select the SQL Server instance and right-click on it. From the available options I choose Properties. In this new window I can set various options regarding memory,processors and security.Do not make changes that affect the whole SQL Server instance unless you know exactly what you are doing.You should consult the product documentation. I will be going through most of these options in the next steps.In the General tab you can see information about the name of SQL Server instance, the language, the number of available processors,the amount of memory.
Have a look at the picture below
This information is read only.
8) We can also click on the Connection Properties in the Server Properties Window.
You can see the Authentication Mode and the username I used to log in.I am connected to the master database. You can also see the product name and version of SQL Server. There is also adequate information regarding the operating system.This information is also read only.
Have a look at the picture below
9) The next option in the Server Properties window is the Memory tab.
Have a look at the picture below
Most of the default options in this tab will be ok for most SQL Server configurations , unless you have heavy loads.Minimum and maximum server memory settings let you control how much memory SQL Server can use. We all know SQL Server is very demanding when it comes to memory.You should change these settings if you have another server application running in the same machine e.g Exchange Server. By the way you should never do that, having another server application running in the same machine where our production SQL Server lives. Some people ask me how much memory SQL Server is using.Well the answer to that should be “All of it”.
If you have 32 Gbytes of RAM in the box that SQL Server lives and there is only one database attached on this server that has a size of 32 Gbytes,SQL Server will cache the entire database in memory.This will improve performance significantly.If you add more memory to the server, make sure you will increase the Maximum server memory as well.
To recap the default memory settings and in particular the maximum server memory setting,if unchanged allows SQL Server to use as much memory as it wants. Well SQL Server listen to calls from the OS and it releases memory back to it so the operating system is not starved.You can change the settings from the Server Properties window.You can also use T-Sql to do that
Have a look at the picture below to see the statements I am using to set the maximum memory to 3800 Mbytes. In my machine the available Physical RAM is 4Gbytes.
Some people suggest that we should limit this number (maximum server memory) to 80-85% of the actual physical memory on a server that has only the Database Engine running.
You could use T-SQL to get the values for minimum and maximum memory settings.
In a new query window in SSMS, type and execute the following statement.
SELECT
name,
minimum,
maximum,
value_in_use
FROM master.sys.configurations
WHERE name IN (‘min server memory (MB)’, ‘max server memory (MB)’);
You can also use a DBCC command to get a snapshot of the current memory status of SQL Server.
In a new query window you could type DBCC MEMORYSTATUS. Have a look in this link for more information.
In the Memory tab you could see another option if you are run a 32-bit server.This option that does not show up in my case since I am running a 64-bit version of SQL Server (as we saw earlier) is Use AWE to Allocate Memory .Do check this option for 32-bit servers with >4GB of memory, and keep it unchecked in all other cases.
Some of the changes I mentioned above will take effect immediately but in some cases you must restart the service.
10) The next set of options I will be looking into are the ones located in the Processors tab.
Have a look at the picture below
You can see that there are 4 available processors in this computer.SQL Server supports many processors and takes advantage of their power.
If you want to change the I/O Affinity or the Processor Affinity you must first uncheck the two boxes under the label Enable processors.
Let me explain what I/O Affinity and Processor Affinity mean. By setting the Processor Affinity to one or more processors you basically dedicate SQL Server to use one or more of these processors.
By setting the I/O Affinity to a specific processor you basically bind I/O threads to this processor.
I would advise against changing the values for I/O Affinity and Processor Affinity . Leave the default settings.
Another option I want to talk about is Boost SQL Server priority.BY enabling this option, SQL Server process will run as High Priority instead of the usual Normal priority. In most cases this will not bring any benefit to your database applications so leave the default value.
Another option I want to talk about is Maximum worker threads.This helps limit the resources consumed on an SQL Server instance by client connections. A new Windows thread is created for each connection.If you set a maximum it can hurt performance.Once that limit is reached any new connection must wait for another connection to close in order to have access to the server.
By default, the max worker threads setting is 0 in SQL Server 2005,SQL Server 2008 and SQL Server 2012.Do not change the default value.
11) In the next tab we will look into settings regarding Security in SQL Server.I have already blogged about SQL Server Security in this post.
Have a look at the picture below.
We have two authentication modes in SQL Server,Windows Authentication Mode and Mixed Mode.
Windows Authentication basically means that a user must log in to a windows domain or a windows local account first before logging into an SQL Server system.Basically a security token is created by Windows when someone logs in and this is passed to SQL Server that “validates” the account name and password based on that token. In a sentence the user logged in SQL Server is checked and identified by Windows.
SQL Server validates the account name and password using the Windows principal token in the operating system.
Windows Authentication disables SQL Server Authentication.I have been asked if you can disable Windows Authentication and the short answer to that is NO.
When possible (Microsoft recommends it) use Windows Authentication only since it is inherently more secure.For example when using Active Directory you can use Group Policies which means we can enforce certain policies like “enforce users to use strong passwords”.Another very useful policy is the “Account Lockout” which locks accounts when too many failed login attempts occur.
We also have Mixed Authentication Mode which basically means Windows Authentication & SQL Server Authentication.
With SQL Server Authentication mode SQL Server manages a seperate set of users and groups to give permissions to.SQL Server logins are not based on Windows accounts,usernames and passwords are created by SQL Server and also stored there.
If you choose to have SQL Server Authentication you would have to keep credentials for your windows domain and SQL Server.When using the SQL Server Authentication make sure you create a long sa password and hide it.
In any case you need to have this authentication mode enabled especially if you need to provide access to your users outside the organisation.SQL Server Authentication does not require a domain controller.
We can obviously change the authentication mode from SSMS but we can also type the following in a new query window
USE master EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2 --Windows Authentication mode USE master EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 1
In the Login auditing section we can select what kind of logins we will audit.
We can also use C2 Audit mode. It configures SQL Server to record attempts to access statements and objects.
C2 Audit mode can still be configured in SQL Server but is now rarely used. It has a negative performance impact through the generation of large volumes of event information.
When you set this option you should be prepared to run out of disk space.If this happens SQL Server will shut itself down.That is why I think it is imperative for everyone to check through jobs and alerts the remaining disk space where our databases live.
12) The next set of options I will be looking into are the ones located in the Connections tab.
Have a look at the picture below
You can set the maximum number of concurrent connections.That means if you set the number to anything other zero,then it is going to limit server resources that can service client connections.Be very careful with that setting.
You can also change the setting “Use query governor to prevent long-running queries”. This means that if we have long-running queries that use all the resources and put huge pressure on SQL Server resources.
We can limit the execution time of every query be checking this option. The default value is 300.That is 300 seconds by the way.That means that if the query does not complete within 300 seconds it will be aborted and will rollback the transaction.
You can also allow remote connections to the server.The default value is 600 seconds, that means 10 minutes.This option is enabled by default in SQL Server 2012 but not in previous versions. Do enable this option if you need it. Do not change the default value.
13) The next set of options I will be looking into are the ones located in the Database Settings tab.
Have a look at the picture below
The first option is the setting for the index fill factor.This specifies the amount of empty space that SQL Server leaves when an index is rebuild or created.This setting should be low (this is a percentage value) for databases that data does not change much and higher in OLTP databases where data is constantly updated,inserted or deleted.
We also have the option to use compressed backups.
Backup compression is available in the SQL Server 2008 R2 Standard edition . When we compress backups we save backup space and also reduce backup times.Obviously we will need more CPU resources to perform compression during a backup or decompression during a restore.One thing to keep in mind is that if we have databases that use data compression or are encrypted, backup compression will not be very efficient. Backup compression does not use row-level or page-level data compression. Backup compression happens at the time of a backup, and it uses its own compression algorithms.
We can also set the “Default backup media retention” option.This option specifies the length of time to retain each backup set.
You can change this setting using T-SQL.
In a new query window type and execute the following statement
USE AdventureWorks2012 ; GO EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE ; GO EXEC sp_configure 'media retention', 90 ; GO RECONFIGURE; GO
Another option we can set is Recovery Interval. This option sets the maximum number of minutes for a database that Microsoft SQL Server needs to recover databases. The default value is 0, which lets SQL Server to automatically configure the recovery interval.
We can also change the Data Default locations option if we choose to.I do not see any reason why you want to change that.This is a setting that you change without fear that you will break something.
14) The next set of options I will be looking into are the ones located in the Advanced tab.
Have a look at the picture below
There are many options we can set in this tab. One of them is “Allow triggers to fire others“.This option is on by default. What actually means is that a trigger will allow to fire another trigger.Sometimes if we are not careful with that option we can get in an infinite loop. Another very important setting is the Optimize for Ad hoc Workloads.You can find more about this very useful option here.
Another option is Max Degree of Parallelism and sets the number of processors used for the execution of a query with a parallel plan.
For best practices and recommendations on Max Degree of Parallelism have a look at this link.
Bear in mind that these are advanced settings and I would not touch them after the initial installation of SQL Server.
15) The next set of options I will be looking into are the ones located in the Permissions tab.
Have a look at the picture below
In this tab we see a list of principals and their permissions they have on the server level.I have selected the user fofo-pc\Nikos. This user is administrator in Windows. This user/principal is also in the fixed server role of sysadmin.You can see the permissions it has on the server.
Make sure you know exactly what each of the options I mentioned does and only then change them.
Hope it helps!!!
Looking into the Resource database in SQL Server October 27, 2012
Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.Tags: Resource database
1 comment so far
In this post I will be looking into the Resource database in SQL Server and what it contains and why it is very important for the normal operation of SQL Server.
I was talking with SQL Server developers the other day and they have never heard or looked into the Resource Database.
The Resource database is available since the SQL Server 2005 version.
I assume that you have installed a version of SQL Server 2008/2012 in your machine.
I also assume that you are familiar with the SQL Server system databases – master,model,tempdb,msdb.
Most people have not heard about the Resource database because it is sort of “secret”,”hidden”.Some people look to find it in the SSMS. It won’t appear there.
You cannot backup the Resource database.Each SQL Server instance has one Resource database.
It is a read-only database that contains all SQL Server system objects.The actual objects are stored in the Resource database.These objects appear logically in the sys schema of each database attached in our SQL Server instance.
Have a look at the picture below. I have logged into my local SQL Server 2012 Developer Edition and expanded the System Views node of the AdventureWorks2012 database.
The Resource database makes upgrades to new versions of SQL Server easier, faster more efficient.The actual name of the database is mssqlsystemresource.mdf. It is located in the C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn path. It is the same path that SQL Server was installed in my machine. If you want to back up this database simply copy and paste it somewhere else using the Windows Explorer.
If we want to find the version of the Resource database, in a new query window type,
SELECT SERVERPROPERTY(‘ResourceVersion’) AS VersionOfResourceDatabase
GO
In my case the output is
VersionOfResourceDatabase
—————————————————————————————————————————————————————————————————————————————————————-
11.00.2845
As you see I am using the built in system function SERVERPROPERTY
If you want to find out when it was the last time the Resource database was updated you can type this bit of TSQL code in a query window in the SSMS.
SELECT SERVERPROPERTY(‘ResourceLastUpdateDateTime’) AS ResourceDatabaseLastUpdated
GO
Ιn my case the output is
ResourceDatabaseLastUpdated
—————————————————————————————————————————————————————————————————————————————————————-
2012-09-01 04:02:07.380
If you want to get the SQL definition for the sys.indexes object, type the following TSQL code in a new query in SSMS
SELECT OBJECT_DEFINITION(OBJECT_ID(‘sys.indexes’))AS Sys_Indexes_Definition
GO
The output is
CREATE VIEW sys.indexes AS
SELECT i.id AS object_id,
i.name AS name,
i.indid AS index_id,
i.type,
n.name AS type_desc,
sysconv(bit, i.status & 0×8) AS is_unique, – IS_IND_UNIQUE
isnull(ds.indepid, 1 – (i.status & 0×100)/0×100) AS dat
Hope it helps!!!




































