SQL SERVER | RTM (Gold, no SP) | SP1 | SP2 | SP3 | SP4 |
---|---|---|---|---|---|
SQL Server 2014 codename Hekaton | |||||
SQL Server 2012 codename Denali | 11.00.2100.60 | 11.00.3000 | |||
SQL Server 2008 R2 codename Kilimanjaro | 10.50.1600.1 | 10.50.2500 | 10.50.4000 | ||
SQL Server 2008 codename Katmai | 10.00.1600.22 | 10.00.2531 | 10.00.4000 | 10.00.5500 | |
SQL Server 2005 codename Yukon | 9.00.1399.06 | 9.00.2047 | 9.00.3042 | 9.00.4035 | 9.00.5000 |
SQL Server 2000 codename Shiloh | 8.00.194 | 8.00.384 | 8.00.532 | 8.00.760 | 8.00.2039 |
SQL Server 7.0 codename Sphinx | 7.00.623 | 7.00.699 | 7.00.842 | 7.00.961 | 7.00.1063 |
Ads
28 October 2013
SQL SERVER Service Packs
25 October 2013
Password Generator Procedure
CREATE PROCEDURE usp_RandomPassword (@pass_len AS int, @password nvarchar(400) OUTPUT)
AS
DECLARE @ValidChar AS nvarchar(400)
SET @ValidChar = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890~!@#$%^&*()_-+={}[]\|:;"<>,.?/'
DECLARE @counter int
SET @counter = 0
SET @password = ''
WHILE @counter < @pass_len
BEGIN
SELECT @password = @password + SUBSTRING(@ValidChar, (CONVERT(int, (LEN(@ValidChar) * RAND() + 1))), 1)
SET @counter += 1
END;
GO
To use the stored procedure to get password:
DECLARE @new_password varchar(50)
EXEC dbo.usp_RandomPassword @pass_len = 10, @password = @new_password out
SELECT @new_password
29 August 2013
Compare Replicated Tables for Differences
Problem:- There is a mismatch in the data which is replicated in a transactional replication.
Solution :-
Step 1:- Need to identify the tables
Step 2:- Compare using TableDiff Utility
Step 3:- Update the same on subscriber as publisher wins always. (applicable only if data is less)
else we need to drop and re- add the article.
Replication Diff Tool Command Line Options
usage: tablediff
-- Source Options --
-sourceserver Source Host
-sourcedatabase Source Database
-sourceschema Source Schema Name
-sourcetable Source Table or View
-sourceuser Source Login
-sourcepassword Source Password
-sourcelocked Lock the source table/view durring tablediff
-- Destination Options --
-destinationserver Destination Host
-destinationdatabase Destination Database
-destinationschema Destination Schema Name
-destinationtable Destination Table or View
-destinationuser Destination Login
-destinationpassword Destination Password
-destinationlocked Lock the destination table/view durring tablediff
-- Misc Options --
-t Timeout
-c Column Level Diff
-f Generate Fix SQL (You may also specify a file name and path)
-q Quick Row Count
-et Specify a table to put the errors into
-dt Drop the error table if it exists
-o Output file
-b Number of bytes to read for blob data types
-strict Strict compare of source and destination schema
-rc Number of retries
-ri Retry interval
Example 1:-
C:\Program Files\Microsoft SQL Server\110\COM>tablediff -sourceserver SQBILS -sourcedatabase MSTER_DATA -sourcetable CustomrCRM-destinationserver ACPB01 -destinationdatabase PCTER_DATA_REPL -destinationtable CustomrCRM
Example 2:-
tablediff -sourceserver PRADYOTHNAP02 -sourcedatabase TELIPHONECALLS -sourcetable student -destinationserver santhoshc -destinationdatabase TELICALL_REPLICA destinationtable Student
Solution :-
Step 1:- Need to identify the tables
Step 2:- Compare using TableDiff Utility
Step 3:- Update the same on subscriber as publisher wins always. (applicable only if data is less)
else we need to drop and re- add the article.
Replication Diff Tool Command Line Options
usage: tablediff
-- Source Options --
-sourceserver Source Host
-sourcedatabase Source Database
-sourceschema Source Schema Name
-sourcetable Source Table or View
-sourceuser Source Login
-sourcepassword Source Password
-sourcelocked Lock the source table/view durring tablediff
-- Destination Options --
-destinationserver Destination Host
-destinationdatabase Destination Database
-destinationschema Destination Schema Name
-destinationtable Destination Table or View
-destinationuser Destination Login
-destinationpassword Destination Password
-destinationlocked Lock the destination table/view durring tablediff
-- Misc Options --
-t Timeout
-c Column Level Diff
-f Generate Fix SQL (You may also specify a file name and path)
-q Quick Row Count
-et Specify a table to put the errors into
-dt Drop the error table if it exists
-o Output file
-b Number of bytes to read for blob data types
-strict Strict compare of source and destination schema
-rc Number of retries
-ri Retry interval
Example 1:-
C:\Program Files\Microsoft SQL Server\110\COM>tablediff -sourceserver SQBILS -sourcedatabase MSTER_DATA -sourcetable CustomrCRM-destinationserver ACPB01 -destinationdatabase PCTER_DATA_REPL -destinationtable CustomrCRM
Example 2:-
tablediff -sourceserver PRADYOTHNAP02 -sourcedatabase TELIPHONECALLS -sourcetable student -destinationserver santhoshc -destinationdatabase TELICALL_REPLICA destinationtable Student
14 August 2013
SQL 2008 R2 database Move to A New SAN
SQL 2008 R2 database Move
We
recently moved our SAN to a new one on a Clustered SQL Server.
First SQL server service could not start and after fixing that SQL
Agent would not start for over a week and finally Microsoft's tech support
helped to find and fix the issue. I found out that I had Missed the last step called Follow-up:
After Moving All System Databases in
these instructions. And if it is a cluster environment then you need to perform this on the active node.
If
this is your first exposure to this process as it was for me, these instructions might be useful to you.
You
can find step by step database (including System DB) move on Microsoft's website http://msdn.microsoft.com/en-us/library/ms345408(v=sql.105).aspx
They are very clear and
extremely helpful and that is what used When we moved from old SAN to a new
one. I added my own notes and some scripts that would help simplify some of the
process such as
detaching and attaching databases, specially if you have lots of DBs to move.
Make a full back of each Database and save it on a
non-clustered drives/location.
1. Run and save the result to run at the end your
process. This generates the attach db statements. You need to to
this first as you will not have access to them once you detach the
databases. Then you can replace the drive and the path to point to the new location.
-- *********** First change the Query Results to text *************
select
Case
When fileid = 1 then ' EXEC sp_attach_db @dbname = N''' + [name]+
''','
+char(13) +
' @filename1 = N''' + filename + ''','
when fileid = 2 then ' @filename2 = N''' + filename + ''';'
+char(13)
+char(13)
end as [Name_path]
from sys.sysaltfiles
where dbid not in (1,2,3,4) -- All dbs except system dbs
2. Detach all databases except, System
DBs. You can run following statements to generate the detach syntax
SELECT 'EXEC MASTER.dbo.sp_detach_db @dbname = N'''
+ Name + ''''FROM sys.sysdatabases
WHERE dbid not in (1,2,3,4)
-- All dbs except system dbs
3. Change the System dbs file location
( except Master Db):
ALTER DATABASE Model MODIFY FILE ( NAME = modeldev, FILENAME =
'Drive Letter:\Path\model.mdf' )
ALTER DATABASE Model MODIFY FILE ( NAME = modellog , FILENAME =
'Drive Letter:\Path\modellog.ldf' )
ALTER DATABASE MSDB MODIFY
FILE ( NAME = MSDBData, FILENAME = 'Drive Letter:\Path\MSDBData.mdf' )
ALTER DATABASE MSDB MODIFY
FILE ( NAME = MSDBLog , FILENAME = 'Drive Letter:\Path\MSDBLog.ldf' )
ALTER DATABASE tempdb MODIFY
FILE ( NAME = tempdev , FILENAME = 'Drive Letter:\PathL\tempdb.mdf' )
ALTER DATABASE tempdb MODIFY
FILE ( NAME = templog , FILENAME = 'Drive Letter:\PathR\emplog.ldf' )
4. Move
all the .mdf and .ldf files to the new location
5. Detach Model, MSDB and Tempdb
SELECT 'EXEC MASTER.dbo.sp_detach_db @dbname = N'''
+ Name + ''''FROM sys.sysdatabases
WHERE dbid in (2,3,4) -- Only system dbs except MASTER DB
To move the master database, follow these steps. If this is clustered server then you need to perfrom these steps on all nodes
- From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.
- In the SQL Server Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.
- In the SQL Server (instance_name) Properties dialog box, click the Advanced tab.
- Edit the Startup Parameters values to point to the planned location for the master database data and log files, and click OK. Moving the error log file is optional.
The parameter value for the data file must follow the -d parameter and the
value for the log file must follow the -l parameter. The following example shows the parameter
values for the default location of the master data and log files.
-dC:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\
DATA\mastlog.ldf
If the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:
-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\
DATA\mastlog.ldf
If the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:
-dE:\SQLData\master.mdf
-lE:\SQLData\mastlog.ldf
-lE:\SQLData\mastlog.ldf
6. Stop the instance of SQL Server
7. Move Model, MSDB, Tempdb and
Master data and log files to the new location
8. Restart the instance of SQL Server
If the msdb database is moved and
the instance of SQL Server is configured for
Database Mail, complete these additional
steps.
- Verify that Service Broker is enabled for the msdb database by running the following query.
SELECT is_broker_enabled
FROM sys.databases
WHERE name = N'msdb'
Important:
Follow-up: After Moving All System Databases
Pasted from <http://msdn.microsoft.com/en-us/library/ms345408(v=sql.105).aspx>
If you have moved all of the system databases to a new drive or
volume or to another server with a different drive letter, make the following
updates. If this is clustered server then you need to perfrom these steps on all nodes
Change the SQL Server Agent
log path. If you do not update this path, SQL Server Agent will fail to
start.
Change the database default location. Creating a new database may fail if the drive letter and path specified as the default location do not exist.
Change the SQL Server Agent Log
Path
From SQL Server Management Studio, in Object Explorer, expand SQL Server Agent. If this is clustered server then you need to perfrom these steps on all nodes
- Right-click Error Logs and click Configure.
- In the Configure
SQL Server Agent Error Logs dialog box, specify the new location of the
SQLAGENT.OUT file. The default location is C:\Program Files\Microsoft SQL
Server\MSSQL10_50.
\MSSQL\Log\.
Change the database default
location
- From SQL Server Management Studio, in Object Explorer, right-click the SQL Server server and click Properties.
- In the Server Properties dialog box, select Database Settings.
- Under Database Default Locations, browse to the new location for both the data and log files.
- Stop and start the SQL Server service to complete the change.
If
any reason you cannot modify the error log path
using Management Studio you modify the registry: Again if this is
clustered server then you need to perfrom these steps on all nodes
- Go to START --> Run and type Regedit
- HKEY_LOCAL_MACHINE -> SOFTWARE -> MICROSOFT -> Microsoft SQL Server ->
MSQL10_50.MSSQLSERVER -> SQLServerAgent and make sure it is
pointing to the new location.
The concurrent snapshot for publication is not available because it has not been fully generated .
Problem:-
The snapshot files for a transactional publication are
immediately removed after being generated by the snapshot agent.
Noticed that status of the publication articles are 3.
Which is wrong either they should be 0 = Inactive, 1 = Subscribed or 2 =
Active.
Solution :-
1) Delete the
subscription and publication.
2) Check the
tables to see if there are still rows for the publication database available:
select * from msdb..MSdistpublishers
select * from distribution..MSpublisher_databases
select * from distribution..MSarticles
where publisher_db =
'distribution'
select *
from distribution..MSsubscriptions
where publisher_db =
'distribution'
3) Delete all
rows that have still the 'publisher_db' from the just deleted publisher
database:
delete from distribution..MSarticles where
publisher_db = 'distribution'
delete from
distribution..MSsubscriptions where publisher_db = 'distribution'
4) Re-create the
publication subscriber and initialize.
Help Links :-
06 August 2013
Script to Enable/Disable Database for Replication
Script to Enable/Disable Database for Replication
You can enable the database for replication using below script.
1
2
3
4
5
| use master exec sp_replicationdboption @dbname = 'sqldbpool' , @optname = 'publish' , @value = 'true' go |
1
2
3
4
5
| use master exec sp_replicationdboption @dbname = 'sqldbpool' , @optname = 'publish' , @value = 'false' go |
02 August 2013
List All Tables Starts With MDT
SP_TABLES
Syntax:-
go
EXEC sp_tables @table_name = 'mdt_%'
Syntax:-
sp_tables [ [ @table_name = ] 'name' ] [ , [ @table_owner = ] 'owner' ] [ , [ @table_qualifier = ] 'qualifier' ] [ , [ @table_type = ] "type" ] [ , [@fUsePattern = ] 'fUsePattern'];
Example :-
Use Database
go
EXEC sp_tables @table_name = 'mdt_%'
DBCC
More on :- http://msdn.microsoft.com/en-us/library/ms188796.aspx
Handy Information :-
Database Console Command statements are grouped into the following categories.
DBCC commands take input parameters and return values. All DBCC command parameters can accept both Unicode and DBCS literals.
DBCC Internal Database Snapshot Usage
Handy Information :-
Database Console Command statements are grouped into the following categories.
Command category | Perform |
---|---|
Maintenance | Maintenance tasks on a database, index, or filegroup. |
Miscellaneous | Miscellaneous tasks such as enabling trace flags or removing a DLL from memory. |
Informational | Tasks that gather and display various types of information. |
Validation | Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages. --------------------------------------------------------------------------- |
DBCC Internal Database Snapshot Usage
The following DBCC commands operate on an internal
read-only database snapshot that the Database Engine creates. This
prevents blocking and concurrency problems when these commands are
executed. For more information, see Database Snapshots (SQL Server).
When you execute one of these DBCC commands, the Database
Engine creates a database snapshot and brings it to a transactionally
consistent state. The DBCC command then runs the checks against this
snapshot. After the DBCC command is completed, this snapshot is dropped.
Sometimes an internal database snapshot is not required or cannot be created. When this occurs, the DBCC command executes against the actual database. If the database is online, the DBCC command uses table-locking to ensure the consistency of the objects that it is checking. This behavior is the same as if the WITH TABLOCK option were specified.
An internal database snapshot is not created when a DBCC command is executed:
DBCC CHECKALLOC | DBCC CHECKDB |
DBCC CHECKCATALOG | DBCC CHECKFILEGROUP |
DBCC CHECKTABLE |
Sometimes an internal database snapshot is not required or cannot be created. When this occurs, the DBCC command executes against the actual database. If the database is online, the DBCC command uses table-locking to ensure the consistency of the objects that it is checking. This behavior is the same as if the WITH TABLOCK option were specified.
An internal database snapshot is not created when a DBCC command is executed:
-
Against master, and the instance of SQL Server is running in single-user mode.
-
Against a database other than master, but the database has been put in single-user mode by using the ALTER DATABASE statement.
-
Against a read-only database.
-
Against a database that has been set in emergency mode by using the ALTER DATABASE statement.
-
Against tempdb. In this case, a database snapshot cannot be created because of internal restrictions.
-
Using the WITH TABLOCK option. In this case, DBCC honors the request by not creating a database snapshot.
-
A read-only filegroup
-
An FAT file system
-
A volume that does not support 'named streams'
-
A volume that does not support 'alternate streams'
Note |
---|
Trying to run DBCC CHECKALLOC, or the equivalent part of DBCC CHECKDB, by using the WITH TABLOCK option requires a database X lock. This database lock cannot be set on tempdb or master and will probably fail on all other databases. |
Note |
---|
DBCC CHECKDB fails when it is run against master if an internal database snapshot cannot be created. |
The sys.dm_exec_requests catalog view
contains information about the progress and the current phase of
execution of the DBCC CHECKDB, CHECKFILEGROUP, and CHECKTABLE commands.
The percent_complete column indicates the percentage complete of the command, and the command column reports the current phase of the execution of the command.
The definition of a unit of progress depends on the current phase of execution of the DBCC command. Sometimes progress is reported at the granularity of a database page, in other phases it is reported at the granularity of a single database or allocation repair. The following table describes each phase of execution, and the granularity at which the command reports progress.
The definition of a unit of progress depends on the current phase of execution of the DBCC command. Sometimes progress is reported at the granularity of a database page, in other phases it is reported at the granularity of a single database or allocation repair. The following table describes each phase of execution, and the granularity at which the command reports progress.
Execution phase | Description | Progress reporting granularity | ||
---|---|---|---|---|
DBCC TABLE CHECK | The logical and physical consistency of the objects in the database is checked during this phase. | Progress reported at the database page level. The progress reporting value is updated for each 1000 database pages that are checked. |
||
DBCC TABLE REPAIR | Database repairs are performed during this phase if REPAIR_FAST, REPAIR_REBUILD, or REPAIR_ALLOW_DATA_LOSS is specified, and there are object errors that must be repaired. | Progress reported at the individual repair level. The counter is updated for each repair that is completed. |
||
DBCC ALLOC CHECK | Allocation structures in the database are checked during this phase.
|
Progress is not reported | ||
DBCC ALLOC REPAIR | Database repairs are performed during this phase if REPAIR_FAST, REPAIR_REBUILD, or REPAIR_ALLOW_DATA_LOSS is specified, and there are allocation errors that must be repaired. | Progress is not reported. | ||
DBCC SYS CHECK | Database system tables are checked during this phase. | Progress reported at the database page level. The progress reporting value is updated for every 1000 database pages that are checked. |
||
DBCC SYS REPAIR | Database repairs are performed during this phase if REPAIR_FAST, REPAIR_REBUILD, or REPAIR_ALLOW_DATA_LOSS is specified, and there are system table errors that must be repaired. | Progress reported at the individual repair level. The counter is updated for each repair that is completed. |
||
DBCC SSB CHECK | SQL Server Service Broker objects are checked during this phase.
|
Progress is not reported. | ||
DBCC CHECKCATALOG | The consistency of database catalogs are checked during this phase.
|
Progress is not reported. | ||
DBCC IVIEW CHECK | The logical consistency of any indexed views present in the database is checked during this phase. | Progress reported at the level of the individual database view that is being checked. |
Subscribe to:
Posts (Atom)
-
ERROR: report server remote procedure call (RPC) end point. Verify that the Report Server Windows service is running, and then retry the o...
-
Error messages: · The process could not execute 'sp_repldone/sp_replcounters' on ' SCKNLSSI '. (Source:...
-
Error Message: Replication-Replication Distribution Subsystem: agent DBA\DBA-EPDW-EPDW-DB6C\DB6C-5 failed. The subscription(S) have been...