SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

James' SQL Footprint

Love SQL Server, Love life.

Backup Master Database

The story came from a question someone asked me.

Does Master database support full recovery mode?

As I remembered, by default, Master database is in "Simple" recovery mode. I never try to put it into Full recovery mode because master database is not updated frequently, and it is very small.…

Read more

0 comments, 4,268 reads

Posted in James' SQL Footprint on 7 July 2012

New in SQL 2012 (1)

Learning new is a interesting thing. Today I read several posts regarding the SQL Server 2012 new feature:

1. Indirect Checkpoint
Prior to SQL Server 2012, SQL Server Database Engine Support 3 type of checkpoints: Automatic, manual and internal. Now there is a new checkpoint type added : Indirect Checkpoint.


Read more

0 comments, 1,999 reads

Posted in James' SQL Footprint on 1 July 2012

Several useful T-SQL Function

1. Using the OVER clause with aggregate functions
The following sample is from BOL:
USE AdventureWorks2008R2;
SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'

Read more

1 comments, 2,277 reads

Posted in James' SQL Footprint on 30 June 2012

Rebuild index with "Alter Index Rebuild" or "DBCC DBREINDEX"

You can rebuild all index for a table with "Alter Index Rebuild" and "DBCC DBREINDEX" .

First, in BOL, for "DBCC DBREINDEX" , it mentions
This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify…

Read more

2 comments, 12,772 reads

Posted in James' SQL Footprint on 23 June 2012

Shrink transaction log file

Sometimes the shrink transaction log file doesn't work.

Before we troubleshoot it, we need to know how the log file works:

Transaction Log Physical Architecture

Here are some tools which can help us investigate the log file issues.
a. DBCC SQLPERF(logspace) : tell you how much transaction log is…

Read more

0 comments, 2,826 reads

Posted in James' SQL Footprint on 17 June 2012

Max Server Memory

Sakthivel Chidambaram recently created a calculator which can find out the max server memory value based on the input.

you can try the tool from http://blogs.msdn.com/b/sqlsakthi/p/max-server-memory-calculator.aspx

Then Jonathan Kehayias made a post to clarify why the calculator doesn't work
Jonathan has another great post relative the max server memory…

Read more

1 comments, 4,261 reads

Posted in James' SQL Footprint on 2 June 2012

Setup windows firewall for SQL Server

On Windows 2008 R2, the windows firewall will turn on by default which causes connection issure for the remote client, here is a script which can turn off the firewall for all SQL Server default port. if there is a named instance, the script should be modified based on the correct setting.

Read more

1 comments, 4,981 reads

Posted in James' SQL Footprint on 1 June 2012

Backup duration statistics

Here is a simple query which can list backup duration statistics for all database, including the max, min, avg of the backup duration.  you can estimate the backup duration by this query when you plan your backup job

  MAX(sizeMB) as MaxBackupFileSizeMB,
  Max(duration) as MaxDurationSEC,
  MAX(speedMBSEC) as MaxSpeedMBperSEC,
  MIN(sizeMB) as…

Read more

0 comments, 1,599 reads

Posted in James' SQL Footprint on 25 May 2012

Ad hoc query optimization in SQL Server

When ad hoc queries are executed in sql server, if it is executed without parameters, and it is simple,  SQL Server parameterizes the query internally to increase the possibility of matching it against an existing execution plan, that's called "Simple Parameterization"

For instance:

--clear plan cache first


Read more

3 comments, 4,465 reads

Posted in James' SQL Footprint on 15 May 2012

Use Powershell script to verify backup files

Before restoring backup, we always verify the backup file first, and run the 3 command below:

    Returns a result set containing all the backup header information for all backup sets on a particular backup device.

    Returns a result set containing a list of…

Read more

0 comments, 6,765 reads

Posted in James' SQL Footprint on 12 May 2012

Backup database in CMS

Sometime you know the database name which you want to backup, but you don't know which server it is on. Normally we will
1. run query on CMS to find out which server the database is on
2. then connect to the server and run backup query.

If you backup…

Read more

0 comments, 934 reads

Posted in James' SQL Footprint on 9 May 2012

Generate restore script automatically by backup history table

I wrote a store procedure which could generate restore script automatically by backup history table. it can help you select a best restore solution base on the backup history table.

For instance,  if you backup the database as the sequence below
ID backup_start_date backup_type
1 2012-04-30 18:28:43.000 Full
2 2012-04-30…

Read more

3 comments, 5,374 reads

Posted in James' SQL Footprint on 30 April 2012

Setup database mirroring and log shipping together

SQL Server has multiple high availability solutions. Comparing with cluster, database mirroring and logshipping are easier to setup, and they don't need special hardware.

here is a great article about how to setup the database mirroring and log shipping together.

In our system, we setup data mirroring with high-safety…

Read more

4 comments, 10,429 reads

Posted in James' SQL Footprint on 21 April 2012

script system object in resource database

When I troulbshooting the logshipping problem, I found many logshipping store procedure can not be found in the master or msdb database. for instance, in the master. [sys].[sp_add_log_shipping_secondary_primary] , it calls sys.sp_MSprocesslogshippingjob to create the copy job, however, sys.sp_MSprocesslogshippingjob is in the resource database, we can not review its code with sp_helptext command.

Read more

0 comments, 886 reads

Posted in James' SQL Footprint on 19 April 2012

Log shipping setup problem

Last week, our log shipping setup had a problem, so I spent some time to dig into the log shipping.

there are 2 monitor tables after logshipping setup
1. log_shipping_monitor_primary
      This table has entries for all primary database on primary server and monitor server

2. log_shipping_monitor_secondary 
      This table has entries…

Read more

1 comments, 1,866 reads

Posted in James' SQL Footprint on 18 April 2012

Why I can not connect sql server ???

       Recently, I installed sql server 2012 on windows 7 laptop, today when I wanted to login sql server with TCP/IP, I got error.

1. Enable TCP protocol
I tried to login sql server by SSMS with Server Name "MachineName\InstanceName",  and select the Network protocol "TCP/IP"

but got error message below


Read more

0 comments, 6,163 reads

Posted in James' SQL Footprint on 10 April 2012

Why shrink sql server data file failed?

Today I need to shrink a big data file, I know shrink file is not a best practise, and it will cause data fragment, but sometimes after you clean the database with deleting old data, you had to shrink file, it should be fine as long as you run rebuild…

Read more

2 comments, 1,764 reads

Posted in James' SQL Footprint on 7 April 2012

Clean SQL Server Cache

           I lives alone, usually, I clean my small apartment at every weekend, wipe the table/firniture with cloth, clean the capet with vacuum cleaner, wash clothes with machine. the clea enviroment makes me feel confortable, and have a good start for the new week.

SQL Server memory cache just like…

Read more

1 comments, 21,998 reads

Posted in James' SQL Footprint on 6 April 2012

SQL Server 2012 license

Not sure if it is a good news or bad news, SQL Server 2012 license shifts from processor-based to core-based.......here are the changes

1. There is no Datacenter Edition anymore, it is merged into Enterpise Edition. Furthermore, Workgroup and Small business Editions are also being retired.

Currently there are only…

Read more

2 comments, 1,809 reads

Posted in James' SQL Footprint on 5 April 2012

Install SQL Server 2012 Book online on local disk

After installing the SQL Server 2012 on my laptop, I found there is only online document available, no local book online document found...........I thought I might not select the local help document during installation, but by researching, it is the new feature of SQL Server 2012..........I don't know what sql…

Read more

7 comments, 24,473 reads

Posted in James' SQL Footprint on 1 April 2012

Newer posts

Older posts