Ads

30 December 2011

Invalid Name Space SSRS 2005

Path for MOF File

C:\Program Files\Microsoft SQL Server\90\Shared

Change Name of RS Instance name,

1. Go to above mentioned location

2. Open the MOF file in Notepad

3. Search below code and replace string to SQL Server NAME.
#pragma namespace ("\\\\.\\root\\Microsoft\\SQLSERVER")
instance of __Namespace
{
    Name = "MSSQLSERVER";
};


4.Save the file, and then exit Notepad.


5.Open a command prompt, and then move to the same folder.

6.Type mofcomp reportingservice.mof, and then press ENTER.


This will solve invalid name space issue


29 December 2011

Max Replication Text Size


When SQL Server is installed, the Max Replication Size value is set by default to 65536. If a record is inserted into a published table that exceeds that value, an error will occur at the application.
The following error will be returned to the calling application:
__________________________________________________________________________________
    Server: Msg 7139, Level 16, State 1, Line 1
Length of text, ntext, or image data (200) to be replicated exceeds configured maximum 10.
The statement has been terminated
__________________________________________________________________________________
In order to prevent this from occurring, Max Text Replication Size needs to be overridden to the maximum allowed, which is 2147483647

Solution :- 


exec sp_configure 'max text repl size', 2147483647

reconfigure with override


21 December 2011

Tail Log Backup

Like any log backup, a tail-log backup is taken by using the BACKUP LOG statement. We recommend that you take a tail-log backup in the following situations:


If the database is online and you plan to perform a restore operation on the database, before starting the restore operation, back up the tail of the log using WITH NORECOVERY:

BACKUP LOG database_name TO WITH NORECOVERY

Note : To avoid an error, the NORECOVERY option is necessary.

If the database is offline and does not start.

Try to take a tail-log backup. Because no transactions can occur at this time, using WITH NORECOVERY is optional. If the database is damaged, use WITH CONTINUE_AFTER_ERROR, as follows:

BACKUP LOG database_name TO WITH CONTINUE_AFTER_ERROR

If the database is damaged, for example, if the database does not start, a tail-log backup succeeds only if the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes.

The following table summarizes these options.


To create a transaction log backup when the database is damaged
 
To create a backup of the currently active transaction log

Execute the BACKUP LOG statement to back up the currently active transaction log, specifying:
The name of the database to which the transaction log to back up belongs.
The backup device where the transaction log backup will be written.

The NO_TRUNCATE clause.

This clause allows the active part of the transaction log to be backed up even if the database is inaccessible, provided that the transaction log file is accessible and undamaged.

Optionally, specify:
The INIT clause to overwrite the backup media, and write the backup as the first file on the backup media. If no existing media header exists, one is automatically written.

The SKIP and INIT clauses to overwrite the backup media, even if there are either backups on the backup media that have not yet expired, or the media name does not match the name on the backup media.

The FORMAT clause, when you are using media for the first time, to initialize the backup media and rewrite any existing media header.

The INIT clause is not required if the FORMAT clause is specified.

Important noteImportant


Use extreme caution when you are using the FORMAT or INIT clauses of the BACKUP statement as this will destroy any backups previously stored on the backup media.



Example

Important noteImportant



The MyAdvWorks_FullRM database is a copy of AdventureWorks2008R2, which uses the simple recovery model. To permit log backups, before taking a full database backup, the database was set to use the full recovery model, using ALTER DATABASE MyAdvWorks_FullRM SET RECOVERY FULL.



This example backs up the currently active transaction log for the MyAdvWorks_FullRM database even though MyAdvWorks_FullRM has been damaged and is inaccessible. However, the transaction log is undamaged and accessible:



BACKUP LOG MyAdvWorks_FullRM


TO MyAdvWorks_FullRM_log1


WITH NO_TRUNCATE;


GO

16 December 2011

DMF

Replication Related DMV

Resource Governor Related DMV


  1. sys.dm_resource_governor_configuration (Transact-SQL)
  2. sys.dm_resource_governor_workload_groups (Transact-SQL)
  3. sys.dm_resource_governor_resource_pools (Transact-SQL)


SQL Server Operating System Related DMV


  1. sys.dm_os_buffer_descriptors (Transact-SQL)
  2. sys.dm_os_memory_pools (Transact-SQL)
  3. sys.dm_os_child_instances (Transact-SQL)
  4. sys.dm_os_nodes (Transact-SQL)
  5. sys.dm_os_cluster_nodes (Transact-SQL)
  6. sys.dm_os_performance_counters (Transact-SQL)
  7. sys.dm_os_dispatcher_pools (Transact-SQL)
  8. sys.dm_os_process_memory (Transact-SQL)
  9. sys.dm_os_hosts (Transact-SQL)
  10. sys.dm_os_schedulers (Transact-SQL)
  11. sys.dm_os_latch_stats (Transact-SQL)
  12. sys.dm_os_stacks (Transact-SQL)
  13. sys.dm_os_loaded_modules (Transact-SQL)
  14. sys.dm_os_sys_info (Transact-SQL)
  15. sys.dm_os_memory_brokers (Transact-SQL)
  16. sys.dm_os_sys_memory (Transact-SQL)
  17. sys.dm_os_memory_cache_clock_hands (Transact-SQL)
  18. sys.dm_os_tasks (Transact-SQL)
  19. sys.dm_os_memory_cache_counters (Transact-SQL)
  20. sys.dm_os_threads (Transact-SQL)
  21. sys.dm_os_memory_cache_entries (Transact-SQL)
  22. sys.dm_os_virtual_address_dump (Transact-SQL)
  23. sys.dm_os_memory_cache_hash_tables (Transact-SQL)
  24. sys.dm_os_volume_stats (Transact-SQL)
  25. sys.dm_os_memory_clerks (Transact-SQL)
  26. sys.dm_os_wait_stats (Transact-SQL)
  27. sys.dm_os_memory_nodes (Transact-SQL)
  28. sys.dm_os_waiting_tasks (Transact-SQL)
  29. sys.dm_os_memory_objects (Transact-SQL)
  30. sys.dm_os_windows_info (Transact-SQL)
  31. sys.dm_os_workers (Transact-SQL)

Service Broker Related DMV


  1. sys.dm_broker_activated_tasks
  2. sys.dm_broker_connections
  3. sys.dm_broker_forwarded_messages
  4. sys.dm_broker_queue_monitors


Index Related DMV

Change Data Capture Related DMV


Dynamic Management Views

These are the Views which gives current state of the SQL Server Machine. There are two types of DMV's


  • Server-scoped DMV: Stored in Master Database
  • Database-scoped DMV: Specific to each database  
Frequently Used DMV's - SERVER SCOPE

a. sys.dm_os_sys_info

This view returns the information about the SQL Server machine, available resources and the resource consumption.
This view returns information like the following:
  1. CPU Count: Number of logical CPUs in the server
  2. Hyperthread-ratio: Ratio of logical and physical CPUs
  3. Physical_memory_in_bytes: Amount of physical memory available
  4. Virtual_memory_in_bytes: Amount of virtual memory available
  5. Bpool_commited: Committed physical memory in buffer pool
  6. OS_Priority_class: Priority class for SQL Server process
  7. Max_workers_thread: Maximum number of workers which can be created

b. sys.dm_os_hosts

This view returns all the hosts registered with SQL Server 2005. This view also provides the resources used by each host.
  1. Name: Name of the host registered
  2. Type: Type of hosted component [SQL Native Interface/OLE DB/MSDART]
  3. Active_tasks_count: Number active tasks host placed
  4. Active_ios_count: I/O requests from host waiting

c. sys.dm_os_schedulers

Sys.dm_os_schedulers view will help you identify if there is any CPU bottleneck in the SQL Server machine. The number of runnable tasks is generally a nonzero value; a nonzero value indicates that tasks have to wait for their time slice to run. If the runnable task counts show high values, then there is a symptom of CPU bottleneck.
SELECT 
scheduler_id,current_tasks_count,runnable_tasks_count 
FROM sys.dm_os_schedulers 
WHERE scheduler_id < 255
The above query will list all the available schedulers in the SQL Server machine and the number of runnable tasks for each scheduler.

d. sys.dm_io_pending_io_requests

This dynamic view will return the I/O requests pending in SQL Server side. It gives you information like:
  1. Io_type: Type of pending I/O request
  2. Io_pending: Indicates whether the I/O request is pending or has been completed by Windows
  3. Scheduler_address: Scheduler on which this I/O request was issued

e. sys.dm_io_virtual_file_stats

This view returns I/O statistics for data and log files [MDF and LDF file]. This view is one of the commonly used views and will help you to identify I/O file level. This will return information like:
  1. Sample_ms: Number of milliseconds since the instance of SQL Server has started
  2. Num_of_reads: Number of reads issued on the file
  3. Num_of_bytes_read: Total number of bytes read on this file
  4. Io_stall_read_ms: Total time, in milliseconds, that the users waited for reads issued on the file
  5. Num_of_writes: Number of writes made on this file
  6. Num_of_bytes_written: Total number of bytes written to the file
  7. Io_stall_write_ms: Total time, in milliseconds, that users waited for writes to be completed on the file
  8. Io_stall: Total time, in milliseconds, that users waited for I/O to be completed
  9. Size_on_disk_bytes: Number of bytes used on the disk for this file

f. sys.dm_os_memory_clerks

This DMV will help how much memory SQL Server has allocated through AWE.
SELECT 
SUM(awe_allocated_kb) / 1024 as [AWE allocated, Mb] 
FROM sys.dm_os_memory_clerks
The same DMV can be used to get the memory consumption by internal components of SQL Server 2005.
SELECT TOP 10 type, 
SUM(single_pages_kb) as [SPA Mem, Kb] 
FROM sys.dm_os_memory_clerks 
GROUP BY type 
ORDER BY SUM(single_pages_kb) DESC

g. sys.dm_os_ring_buffers

This DMV uses RING_BUFFER_RESOURCE_MONITOR and gives information from resource monitor notifications to identify memory state changes. Internally, SQL Server has a framework that monitors different memory pressures. When the memory state changes, the resource monitor task generates a notification. This notification is used internally by the components to adjust their memory usage according to the memory state.
SELECT 
Record FROM sys.dm_os_ring_buffers 
WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
The output of the above query will be in XML format. The output will help you in detecting any low memory notification.
RING_BUFFER_OOM: Ring buffer oom contains records indicating server out-of-memory conditions.

Frequently Used DMV's - DB SCOPE



a. sys.dm_db_file_space_usage

This DMV provides the space usage information of TEMPDB database.

b. sys.dm_db_session_space_usage

This DMV provides the number of pages allocated and de-allocated by each session for the database

c. sys.dm_db_partition_stats

This DMV provides page and row-count information for every partition in the current database.
The below query shows all counts for all partitions of all indexes and heaps in the MSDB database:
USE MSDB;
GO
SELECT * FROM sys.dm_db_partition_stats;
The following query shows all counts for all partitions of Backup set table and its indexes
USE MSDB
GO
SELECT * FROM sys.dm_db_partition_stats 
WHERE object_id = OBJECT_ID('backupset');

d. sys.dm_os_performance_counters

Returns the SQL Server / Database related counters maintained by the server.
The below sample query uses the dm_os_performance_counters DMV to get the Log file usage for all databases in KB.
SELECT instance_name
,cntr_value 'Log File(s) Used Size (KB)'
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Log File(s) Used Size (KB)'

3. INDEX Related DMV

This section details the DMVs associated with SQL Server Databases. These DMVs will help to identify database space usages, Partition usages, Session information usages, etc.

a. sys.dm_db_index_usage_stats

This DMV is used to get useful information about the index usage for all objects in all databases. This also shows the amount of seeks and scan for each index.
SELECT object_id, index_id, user_seeks, user_scans, user_lookups 
FROM sys.dm_db_index_usage_stats 
ORDER BY object_id, index_id
All indexes which have not been used so far in as database can be identified using the below Query:
SELECT object_name(i.object_id), 
i.name, 
s.user_updates, 
s.user_seeks, 
s.user_scans, 
s.user_lookups
from sys.indexes i 
left join sys.dm_db_index_usage_stats s 
on s.object_id = i.object_id and 
i.index_id = s.index_id and s.database_id = 5
where objectproperty(i.object_id, 'IsIndexable') = 1 and
s.index_id is null or
(s.user_updates > 0 and s.user_seeks = 0 
and s.user_scans = 0 and s.user_lookups = 0)
order by object_name(i.object_id)
Replace the Database_id with the database you are looking at.

4. Execution Related DMV

Execution related DMVs will provide information regarding sessions, connections, and various requests which are coming into the SQL Server.

a. sys.dm_exec_sessions

This DMV will give information on each session connected to SQL Server. This DMV is similar to running sp_who2 or querying Master..sysprocesses table.
SELECT
session_id,login_name,
last_request_end_time,cpu_time
FROM sys.dm_exec_sessions
WHERE session_id >= 51All user Sessions

b. sys.dm_exec_connections

This DMV shows all the connection to SQL Server. The below query uses sys.dm_exec_connections DMV to get connection information. This view returns one row for each user connection (Sessionid > =51).
SELECT
connection_id,
session_id,client_net_address,
auth_scheme
FROM sys.dm_exec_connections

c. sys.dm_exec_requests

This DMV will give details on what each connection is actually performing in SQL Server.
SELECT
session_id,status,
command,sql_handle,database_id
FROM sys.dm_exec_requests
WHERE session_id >= 51

d. sys.dm_exec_sql_text

This dynamic management function returns the text of a SQL statement given a SQL handle.
SELECT 
st.text
FROM
sys.dm_exec_requests r 
CROSS APPLY 
sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.session_id = 51


Execute Permissions For User

  • GRANT VIEW SERVER STATE to
  • GRANT VIEW DATABASE STATE to

SQLServerCentral

In a SQL Server 2008 R2 clustered node, how many instances can you install?

The maximum number of instances on a clustered node is 25 in SQL Server 2008 R2. A non-clustered computer can have 50 instances.

In SQL Server 2008 R2, can you run a full database backup and a filegroup backup at the same time?


Certain operations cannot be run concurrently in SQL Server 2008 R2. A full backup and a file backup must be done at separate times.

Which network protocol is deprecated in SQL Server 2008 R2?

The VIA protocol has been marked as deprecated in SQL Server 2008 R2 and will be removed in a future version. You should not be using this in any future development or migrations.


When you execute DBCC CHECKDB, which of the following DBCC commands are executed?

DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKTABLE


Can you disable full-text search on a user database that was created in Microsoft SQL Server 2008 or higher?                 

No,User databases are always enabled for full-text seach post SQL Server 2008. Also, sp_fulltext_database cannot (and should not) be used to enable/disable full-text search.

Which of these commands will find the oldest open transaction?

DBCC OPENTRAN                 


Your SQL Server 2008 instance crashes during disaster recovery, and you need to rebuild the master database. How should you do this?


Use setup.exe with the REBUILDDATABASE action.

         In SQL Server 2000, rebuildm.exe was the recommended method for rebuilding a master database. This was replaced in SQL Server 2005 and 2008 with the REBUILDDATABASE action of setup.exe, although the syntax differs. The Dedicated Administrator Connection (DAC) allows DBAs to connect to a potentially unresponsive instance using SSMS or SQLCMD. If the DAC can connect to the instance the Master database is guaranteed to be available, as the instance will be running.
For more information see: Rebuilding the master database in SQL Server 2000 - http://msdn.microsoft.com/en-us/library/aa213831%28v=sql.80%29.aspx
Rebuilding the master database in SQL Server 2005 - http://msdn.microsoft.com/en-us/library/ms143269%28v=sql.90%29.aspx/a>
Rebuilding the master database in SQL Server 2008 -
http://msdn.microsoft.com/en-us/library/dd207003%28v=SQL.100%29.aspx/a>
Using a Dedicated Administrator Connection -
http://msdn.microsoft.com/en-us/library/ms189595.aspx/a>

What data is stored in the sys.dm_database_encryption_keys DMV?

The encryption state of all encrypted databases on the instance.                 



14 December 2011

Difference between a Cumulative Update and Service Pack for SQL Server

Cumulative Update


Cumulative Updates are primarily released for hotfix purposes.



Service Pack


Service Pack is they include actual changes or new enhancing features.

It is being used by another process

The process cannot access the file 'C:\Windows\SysWOW64\perf-MSSQL10_50.MSSQLSERVER-sqlagtctr.dll' because it is being used by another process

Type: Bug
ID: 576412
Opened: 7/19/2010 8:34:44 AM
Access Restriction: Public
1
Workaround(s)
3
User(s) can reproduce this bug
Watch List
Watch List
Windows 7 x64, SQL 2008 SP1 CU8 upgrade to SQL 2008 R2 RTM. i get this error message everytime:

The following error has occurred:

The process cannot access the file 'C:\Windows\SysWOW64\perf-MSSQL10_50.MSSQLSERVER-sqlagtctr.dll' because it is being used by another process.

Click 'Retry' to retry the failed action, or click 'Cancel' to cancel this action and continue setup.

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.50.1600.1&EvtType=0x60797DC7%25400x24C2C4E7

I tried to use process explorer to determine what has this locked, but i am starting to suspect some type of redirection is not occurring?

WORKAROUND :- 


1) Stop SQL Services.
2) Rename the file
Original name: perf-MSSQL10_50.MSSQLSERVER-sqlagtctr.dll
Renamed: OLD_perf-MSSQL10_50.MSSQLSERVER-sqlagtctr.dll
and was able to 'Retry' the Repair SQL 2008 R2 installation and continue.

This works.

13 December 2011

Delete Secondary File

To shrink a data or log file

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
  2. Expand Databases and then right-click the database that you want to shrink.
  3. Point to Tasks, point to Shrink, and then click Files.
  4. Select the file type and file name.
  5. Optionally, select the Release unused space check box.
    Selecting this option causes any unused space in the file to be released to the operating system and shrinks the file to the last allocated extent. This reduces the file size without moving any data.
  6. Optionally, select the Reorganize files before releasing unused space check box. If this is selected, the Shrink file to value must be specified. By default, the option is cleared.
    Selecting this option causes any unused space in the file to be released to the operating system and tries to relocate rows to unallocated pages.
  7. Optionally, enter the maximum percentage of free space to be left in the database file after the database has been shrunk. Permissible values are between 0 and 99. This option is only available when Reorganize files before releasing unused space is enabled.
  8. Optionally, select the Empty file by migrating the data to other files in the same filegroup check box.
    Selecting this option moves all data from the specified file to other files in the filegroup. The empty file can then be deleted. This option is the same as executing DBCC SHRINKFILE with the EMPTYFILE option.
  9. Click OK.

09 December 2011

Backup Verification Script - Backup when and what type

Select Distinct convert(varchar(25),@@Servername) as Servername,
            convert(varchar(30),e.database_name) as DBname,
            convert(varchar(20),convert(sysname,DatabasePropertyEx(e.database_name,'Recovery') )) as Recovery_model,
database_creation_date,backup_start_date,backup_finish_date,
           
(Select  convert(varchar(25),Max(backup_finish_date) , 100)
              From msdb..backupset a
              Where a.database_name=e.database_name
                and a.server_name  = @@servername
                and type='D'
              Group by a.database_name) Database_Bk,

             (Select convert(varchar(25),Max(backup_finish_date) , 100)
                From msdb..backupset b
               Where b.database_name=e.database_name
                and b.server_name  = @@servername
                 and type='L' Group by b.database_name) Log_Bk,

             (Select convert(varchar(25),Max(backup_finish_date) , 100)
                From msdb..backupset c
               Where c.database_name=e.database_name
                and c.server_name  = @@servername
                 and type='I' Group by c.database_name) Diff_Bk,

             (Select convert(varchar(25),Max(backup_finish_date) , 100)
                From msdb..backupset d
               Where d.database_name=e.database_name
                and  d.server_name  = @@servername
                 and type='F' Group by d.database_name) File_Bk

 From msdb..backupset e
Where e.database_name Not in ('tempdb','pubs','northwind','model')
  and e.server_name = @@Servername
  and e.database_name in (Select Distinct name from master..sysdatabases)

--never backed up
Union all
select Distinct convert(varchar(25),@@Servername) as Servername,
            convert(varchar(30),name) as DBname,
            convert(varchar(20),convert(sysname,DatabasePropertyEx(name,'Recovery'))),

NULL, NULL , NULL, NULL, NULL, NULL, NULL
from master..sysdatabases as record
where name not in (select distinct database_name from msdb..backupset)
and name not in ('tempdb','pubs','northwind','model')
order by 1,2

Script to find Recovery Model and File Location

select *  from
(select sf.fileid, sd.name, sd.cmptlevel, sf.filename,sf.filename as secfile , DATABASEPROPERTYEX (sd.name,'Recovery') as Rec_model
 from sysaltfiles sf join sysdatabases sd on sf.dbid=sd.dbid ) as s --where s.rec_model='Simple'

Script to find Recovery Model and File Location

select *  from
(select sf.fileid, sd.name, sd.cmptlevel, sf.filename,sf.filename as secfile , DATABASEPROPERTYEX (sd.name,'Recovery') as Rec_model
 from sysaltfiles sf join sysdatabases sd on sf.dbid=sd.dbid ) as s --where s.rec_model='Simple'

Databases Sizes and Current DB Table Size

The following query will create a store proc SP_PH_DATABASE_USAGE, which will give the DataBase Sizes and Table Sizes for the current DataBase.




USE MASTER
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'SP_PH_DATABASE_USAGE' AND XTYPE = 'P')
DROP PROC SP_PH_DATABASE_USAGE 
GO


CREATE PROCEDURE SP_PH_DATABASE_USAGE AS
SET NOCOUNT ON
IF EXISTS( SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##DATASPACEFORDB' )
DROP TABLE ##DATASPACEFORDB
IF EXISTS( SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##TEMPFORDB' )
DROP TABLE ##TEMPFORDB




CREATE TABLE ##DATASPACEFORDB
 ( FILEID NUMERIC,
FILEGRP NUMERIC,
TOTEXT NUMERIC,
USDEXT NUMERIC,
   LFILENM VARCHAR( 100),
   PFILENM VARCHAR( 100)
   )
INSERT ##DATASPACEFORDB
 EXEC( 'DBCC SHOWFILESTATS WITH NO_INFOMSGS')


SELECT FILEID,GROUPNAME,(TOTEXT*64)/1024 AS 'TOTALSPACE_MB',(USDEXT*64)/1024 AS 'SPACEUSED_MB',LFILENM,PFILENM 
INTO ##TEMPFORDB FROM ##DATASPACEFORDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID
ORDER BY GROUPNAME


INSERT INTO ##TEMPFORDB 
SELECT '50','TOTAL DATA FILE SIZE IN GB',SUM((TOTEXT*64)/1024)/1024 ,SUM((USDEXT*64)/1024)/1024  ,'',''
FROM ##DATASPACEFORDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID




IF EXISTS( SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##DATASPACEFORTEMPDB' )
DROP TABLE ##DATASPACEFORTEMPDB


IF EXISTS( SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##TEMPFORTEMPDB' )
DROP TABLE ##TEMPFORTEMPDB


CREATE TABLE ##DATASPACEFORTEMPDB
 ( FILEID NUMERIC,
FILEGRP NUMERIC,
TOTEXT NUMERIC,
USDEXT NUMERIC,
   LFILENM VARCHAR( 100),
   PFILENM VARCHAR( 100)
   )
INSERT ##DATASPACEFORTEMPDB
 EXEC( 'USE TEMPDB DBCC SHOWFILESTATS WITH NO_INFOMSGS')


SELECT FILEID,GROUPNAME,(TOTEXT*64)/1024 AS 'TOTALSPACE_MB',(USDEXT*64)/1024 AS 'SPACEUSED_MB',LFILENM,PFILENM 
INTO ##TEMPFORTEMPDB FROM ##DATASPACEFORTEMPDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID
ORDER BY GROUPNAME


INSERT INTO ##TEMPFORTEMPDB 
SELECT '100','TOTAL DATA FILE SIZE IN GB',SUM((TOTEXT*64)/1024)/1024 ,SUM((USDEXT*64)/1024)/1024  ,'',''
FROM ##DATASPACEFORTEMPDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID


IF EXISTS( SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##TMPLOGSPACE' )
DROP TABLE ##TMPLOGSPACE


CREATE TABLE ##TMPLOGSPACE 
(
DATABASENAME VARCHAR(100),
LOGSIZE VARCHAR(100),
LOGSPACEUSED VARCHAR(100),
STATUS VARCHAR(100)
)


INSERT INTO ##TMPLOGSPACE 
    EXEC ('DBCC SQLPERF ( LOGSPACE ) WITH NO_INFOMSGS')




SELECT '                        ' AS 'DATABASENAME',* INTO ##FINALTABLEFORDB  FROM ##TEMPFORDB WHERE 1=2


INSERT INTO ##FINALTABLEFORDB  
SELECT DB_NAME(),* FROM ##TEMPFORDB 


INSERT INTO ##FINALTABLEFORDB  
 SELECT 'TEMPDB',* FROM ##TEMPFORTEMPDB


INSERT INTO ##FINALTABLEFORDB  
SELECT DATABASENAME,101,'TOTAL LOG SIZE IN GB',CONVERT(NUMERIC(20),LOGSIZE)/1024 , (CONVERT(NUMERIC(20),((CONVERT(NUMERIC(20),LOGSPACEUSED)*LOGSIZE)/100)))/1024,'',''  
FROM ##TMPLOGSPACE WHERE DATABASENAME IN (DB_NAME(),'TEMPDB' )


SELECT UPPER(DATABASENAME),FILEID,GROUPNAME,CONVERT(NUMERIC(20),TOTALSPACE_MB) AS TOTALSPACE_MB,CONVERT(NUMERIC(20),SPACEUSED_MB) AS SPACEUSED_MB ,CONVERT(NUMERIC(20),(TOTALSPACE_MB-SPACEUSED_MB)) AS 'FREESPACE_MB' , LFILENM,PFILENM 
FROM ##FINALTABLEFORDB  ORDER BY 1,2


DROP TABLE ##DATASPACEFORDB, ##TEMPFORDB
DROP TABLE ##DATASPACEFORTEMPDB, ##TEMPFORTEMPDB
DROP TABLE ##TMPLOGSPACE, ##FINALTABLEFORDB


 --temp table for further querying
CREATE TABLE #temp(
rec_id int IDENTITY (1, 1),
table_name varchar(128),
nbr_of_rows int,
data_space decimal(15,2),
index_space decimal(15,2),
total_size decimal(15,2),
percent_of_db decimal(15,12),
db_size decimal(15,2))


-- Get all tables, names, and sizes
EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace '?'",
@command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)"


-- Set the total_size and total database size fields
UPDATE #temp
SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)


-- Set the percent of the total database size
UPDATE #temp
SET percent_of_db = (total_size/db_size) * 100


-- Get the data
SELECT *
FROM #temp
ORDER BY total_size DESC


-- Comment out the following line if you want to do further querying
DROP TABLE #temp


GO

Network Protocols

The VIA protocol has been marked as deprecated in SQL Server 2008 R2 and will be removed in a future version. You should not be using this in any future development or migrations.

06 December 2011

T-Replication - ON Demand Schedule, Without SnapShot

Please check the link below,

To Find Path Of SQL Log File

declare @dir nvarchar(4000) 


exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'SQLArg1', @dir output, 'no_output' 


if @dir is null 
begin
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @dir output, 'no_output'
select @dir = @dir + N'\LOG'
end


select @dir


OR

SELECT SERVERPROPERTY('ErrorLogFileName')

To Find Path Of SQL Log File

declare @dir nvarchar(4000) 

exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'SQLArg1', @dir output, 'no_output' 

if @dir is null 
begin
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @dir output, 'no_output'
select @dir = @dir + N'\LOG'
end

select @dir

29 November 2011

HELP! - PF Details

REGIONAL OFFICE [KARNATAKA (Bangalore)] Bhavishyanidhi Bhavan, No. 13, Raja Ram Mohan Roy Road,
P.B. No. 25146, Bangalore – 560 025 (KN).
General – EPBAX : 22216566, 22216596, 22214901, 22214961
E-mail: rpfcbglr@dataone.in / rpfcbglr@vsnl.net
Designation Phone Fax R.P.F.C 080-22277700 080-22273991 P.R.O 080-22279130        22384722
- SUB REGIONAL OFFICE  [KARNATAKA (Bangalore)]
BOMMASANDRA 2nd Floor, CMC Building, Begur Road, Bommanahally, Hosur Road, Bommasandra, Bangalore – 560 068.
E-mail: srobmsbglr@dataone.in
Designation Phone Fax A.P.F.C (OIC) 080-25734492 080-25733817
K.R PURAM 36, “Lakshmi Complex”, NH-4 (Opposite Syndicate Bank), K.R. Puram,
Bangalore – 560 036. E-mail: epfokrpbglr@hotmail.com
Designation Phone Fax A.P.F.C (OIC) 080-25611955        25613382
080-25610209 P.R.O 080-25617629 -
PEENYA Plot No. S(1), 1st Cross, 1st Phase, Peenya Industrial Estate, Bangalore – 560 058.
E-mail: rpfcpeenya@dataone.in
Designation Phone Fax R.P.F.C 080-23721377 080-23721377 General 080-23721355         23721355


More on :- http://www.sarkaritel.com/ministries/min_labour/epfo_index.htm

To Check status and other info :- http://www.epfindia.com/