SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

Featured Script

The Voice of the DBA

Data Philanthropy

The idea of philanthropy is giving back to the world, doing some good because of the success you have had or gifts you've received. Imagine that we thought not about giving our time or money, but our data instead.

I read this piece on data philanthropy that talks about the possibilities of improvement in the public service and applications we have from philanthropy. However the piece talks not about more resources, but more data, coming from the private sector. Data from cell phone companies, transportation companies, and more that can help us better understand how the world is functioning, how it interacts, and how that might affect the public goods and services.

I like the idea of Open Data that exposes more information from our government to the private sector for incorporation in applications, for additional analysis by private entities, and for the transparency that our government is acting in our collective best interests. However you believe government ought to function, without more data about how it does function and uses its resources, we can't know about which things we should seek to influence.

However I think we have a tremendous amount of data being collected by companies that can help government run more efficiently, and making the corporate decision to share that data can help improve the communities in which a company does business. We know that much of our data has information, but we can't take advantage of all the information, and may not want to. Perhaps donating some of this data to the collective good is a way to adhere to, and reinforce, the social responsibility many companies include in their annual reports.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums


Video and Audio versions

Toda's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com. You can also follow Steve Jones on Twitter   to find links and database related items and announcements.

Follow Steve Jones on Twitter to find links and database related items and announcements.

Steve Jones

Windows Media Video (13.6MB) feed

MP4 iPod Video (18.3MB) feed

MP3 Audio (3.3MB) feed

Feeds are available at iTunes and Mevio

To submit an article, rant or editorial,
log in to the Contribution Center

ADVERTISEMENT
sqlmonitor

Free eBook: SQL Server Concurrency

Every DBA must understand SQL Server concurrency and how to troubleshoot any issues. Kalen Delaney's eBook explains all - download it today.

SQL Developer Bundle

12 essential tools for database professionals

The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.

sqlinthecity

SQL in the City – Free SQL Server Training the Red Gate way

Red Gate is hosting free seminars and events in the US this summer and fall. Learn about best practices for SQL Server database development and administration from top SQL Server MVPs. Find out more and register

Featured Contents

 

What is Biml? - Level 1

Andy Leonard from SQLServerCentral.com

An introduction to the Biml language from Andy Leonard that helps More »


 

Biml Workshop presented by Varigence and Linchpin People

Press Release

Business Intelligence Markup Language (Biml) automates your BI patterns and eliminates the manual repetition that consumes most of your time. On October 15th come see why BI professionals around the world think Biml is the future of data integration and BI. More »


 

Free eBook: Understanding SQL Server Concurrency

Press Release from Red-Gate

When you can’t get to your data because another application has it locked, a thorough knowledge of SQL Server concurrency will give you the confidence to decide what to do. More »


 

SQL Saturday #222

Press Release from SQLServerCentral.com

SQL Saturday is coming to Sacramento on July 27, 2013. Join us for a free day of SQL Server training and networking. This SQL Saturday also features a paid-for full day pre-con session with Kalen Delaney More »


 

SQL Server Transactions and Locking – Part 2

Additional Articles from SQL Server Performance

This article looks at SQL Server locking and transaction isolation levels, how to set the transaction isolation level, and how some isolation levels use locking, while others use row versioning. It also explains what type of locks data update requires. More »

Question of the Day

Today's Question (by Carlo Romagnano):

What is the result of this batch? (select two)

--batch ONE
select 1
return select 2
select 3
go
--batch TWO
select 4
return (select 5)

Think you know the answer? Click here, and find out if you are right.


We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: T-SQL.

Did you miss yesterday's question, Reset of Identity? Answer now.

We'd love to give you credit for your own question and answer.
To submit a QOD, simply log in to the Contribution Center.

ADVERTISEMENT

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks. Get your copy from Amazon today.

Featured Script

The Automated DBA: Space Usage Snapshotter (sysadmin; central DB)

Jesse Roberge from SQLServerCentral.com

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
Util_BuildSpaceLog
By Jesse Roberge - YeshuaAgapao@Yahoo.com

The SpaceLog builder
Records database space usage and buffer cache usage statistics into the SpaceLog_* tables.
Keeps a record of database and table growth for reporting of growth trends.
Stats include Reserved, Used, Data, and buffer cache page counts for row, lob (VarChar-Max, text etc), and overflow data (data rows with varchar columns going over 8000 bytes).
Stats also include some computed columns such as BTree (Used-Data), unused (Reserved-Used), and sums of row+lob+overflow for each of reserved, used, data, and buffer.
The database, dataspace, schema, and table levels give stats for the nonclustered index and the table itself (heap or clustered index).
Designed to run and record to a central 'admin' database location.
Requires VIEW_SERVER_STATE due to the querying of sys.dm_os_buffer_descriptors for buffer cache usage stats. DB-owner does not have this permission.
Sysadmin does have this permission. VIEW_SERVER_STATE can be granted as a separate permission to some or all dbo users.

Required Input Parameters
None

Optional Input Parameters
@GatherDatabaseName sysname='', Name of the database to gather the space usage stats from. If omitted, the name will be looked up from the ID. If both ID and name are omitted, the ID/Name of the current database is used.
@GatherDatabaseID int=0, ID of the database to gather the space usage stats from. If omitted, the ID will be looked up from the name. If both ID and name are provided, the ID takes precedence.
@RecordDatabaseName sysname='', Name of the database to record the space usage stats to. If omitted, the name will be looked up from the ID. If both ID and name are omitted, the ID/Name of the current database is used.
@RecordDatabaseID int=0, ID of the database to record the space usage stats to. If omitted, the ID will be looked up from the name. If both ID and name are provided, the ID takes precedence.
@RecordSchemaName sysname='', Name of the schema to record the space usage stats to. If omitted, the name will be looked up from the ID. If both ID and name are omitted, the 'dbo' (ID=1) schema is used.
@RecordSchemaID int=0, ID of the schema to record the space usage stats to. If omitted, the ID will be looked up from the name. If both ID and name are provided, the ID takes precedence.
@UpdateUsage tinyint=0 Default and recommended to be off. Use only if you must have the most accurate and up to date numbers. Will run DBCC UpdateUsage to scan every table in the database to re-count all allocations and rows, which can hog disk IO for serveral hours.

Usage
EXECUTE Admin.Util_BuildSpaceLog
@GatherDatabaseName='Baseball',
@RecordDatabaseName='Admin',
@RecordSchemaName='Admin',

EXECUTE Admin.Util_BuildSpaceLog
@GatherDatabaseID=10,
@RecordDatabaseID=7,
@RecordSchemaID=2,
@UpdateUsage=1

DECLARE @SQL nVarChar(4000)
SET @SQL=''
SELECT @SQL=@SQL+'EXECUTE Admin.Util_BuildSpaceLog @GatherDatabaseName=''' + name + ''', @RecordDatabaseName=''Admin'', @RecordSchemaName=''Admin''' + CHAR(13)+CHAR(10)
FROM sys.databases
WHERE database_id<>2
EXECUTE (@SQL)
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

More »

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

Total memory allocation and total memory consumption for SQL Server - Hi all, I have to find out the total memory allocated to SQL Server and how much memory SQL is actually...

need solution and feedback to the below question!! - here is the sample data . create table empp(eno int,ename varchar(25),dept varchar(3)) insert into empp values(101,'raghava','hr') insert into empp values(102,'krish','hr') insert into empp values(103,'venkat','fin') create...

Management Studio 2012-Adding new Logins - Hi: I am new to SQL Server and am trying to create a new login ID and password with permissions. I...

Log Shipping Jobs on Secondary not getting created - Hi all: I'm trying to test my understanding of log shipping here. I'm currently trying to get it working on 2...

sql backup to avamar - Is anyone using avamar backups to do live backups of sql server? are there any issues, are there problems? AW

SQL Server 2005 : Backups

Odd permissions issue when backing up to a network location - I have a 2005 box and 3 2008 boxes. All 2008 boxes backup fine to the network location however, I'm...

SQL Server 2005 : Business Intelligence

Minor change to SSIS connection manager causes error - I have an SSIS package that has been working fine up until now. I added a column to the flat...

SSIS - Transfer SQL Server Objects Task - Performance - Hi, We have daily schedule to move around 20 tables which have huge data to another Database. The issue is The...

How to change the connection string of child packages in Execute Package task? - Hi Friends, I am tryting to execute multiple packages so i am using Execute Package Task to run the packages...

SQL Server 2005 : Development

FORXML not retrieving complete xml - Hi, I am having the below XML <marketDataSeries xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://pricesandcurves.ist.bp.com/xsd/CSL/IST/marketData/v1" xsi:schemaLocation="http://pricesandcurves.ist.bp.com/xsd/CSL/IST/marketData/v1 marketData.xsd" seriesID="urn:pc:FO.E.34639"> <observationDate>2013-07-15T00:00:00</observationDate> <timeZone>Londo

SQL Server 2005 : Working with Oracle

Linked server querying error - Hi guys, recently i need to start to look after some of the linked server query . i got a instance...

SQL Server 2005 : SQL Server 2005 General Discussion

query - show tables on some condition. - Hello world. I need to show all tables from my database that meet some conditions. For example tables with entries...

SQL Server 2005 : SQL Server Express

How do I allow all logged in users to access a SQL Express 2008 R2 database? - I'm working on a new WPF application, which uses a SQL Server 2008 R2 Express database, stored locally on the...

SQL Server 2005 : T-SQL (SS2K5)

cursor for inserting one record at a time - i need to insert records from table1 to table 2 out of 15 columns from table1 i need to insert 6...

SQL Server 7,2000 : Administration

Transnational replication vs transnational updatable replication - Hi, Can we add objects to subscriber in Transnational replication. Can we update tables in subscriber in Transnational updatable replication.

SQL Server 7,2000 : T-SQL

Parsing Last,First Middle Name - Hi, I need to parse last name,first name space middle name. Example A - DOE,JOHN Example B - DOE,JOHN A I am able to parse the...

SQL Server 2008 : SQL Server 2008 - General

How to measure the SQL Server MTL Memory Usage. - Hi , Having issues with the following scenario, pls help if anyone can Measure the MTL Usage. [b][u]DB Environment Details[/u][/b] Windows/SQL Server 2008...

A produce that reports versions, editions and product name of SQL Server installs - Hello, I am looking for a product that reports versions, editions and product names of SQL Server installs and instances in...

Need to restore one file into a database - Hi all, First of all, this is development server. We have a database of a size of over 1 TB which...

SSMS can't connect to SQL Server, no error, no end. - Hi All, I really need help on this. One QA's computer can't connect to any SQL Server instances using SSMS. When...

Query The start up type of a service - I am just putting together a list of security checks which will eventually have a front end to them for...

service accounts - what are the difference between configuring the accounts as network and local accounts while installing sql ?

LS - i added one secondary database file on the primarydatabase where the db is configured in logshipping on E drive , how...

latch - can i know the difference b/w latch vs lock ?

reasons - what are the possible reasons when a query is timed out ? what are the possible reasons for not truncating the...

Obtaining SSMS errors when a SSRS report returns an error, without logging on to SSMS each time. - Hello people, I have a problem in that if some SSRS report runs a procedure which returns an error, the message...

Foregin Key - Hi, If I disable a Fk and then renable it with Check Check; will this lock the table out while the...

Does compressed data stay compressed in transactional replication SQL 2008 R2 - Does compressed data stay compressed in transactional replication SQL 2008 R2? We are planning on using transactional replication to replicate SQL...

Scan count vs logical reads - I have two versions of a query. 1. Scan count 136, logical reads 2776907,CPU time = 230848 ms, elapsed time = 55753 ms. 2....

dynamic index rebuild based on fragmentation level - Hi Guys, Anyone have a dynamic script which will list and rebuild indexes > 30% avg fragmentation. Seen it somewhere but can't...

service pack on cluster - I have 2node cluster and when we are applying the service pack on passive node suddenly active cluster was down...

Role Permission - Hi all Simple question, If I grant user permission to role "DbCreator", does that user have access to create databases and...

create linked server using windows authentication - Hi, I have a peculiar requirement but was not able to go much ahead. Setup a linked server on InstA(mixed...

DATABASE BACK UP PROBLEM - Correct me ,, if i am posting my problem in a wrong forum. Well,,, Here i in my company i am using full...

Tempdb files - Load test - How to arrive at the number of tempdb files needed for a SQL 2008 2 processor server. Everyone mentions about...

quick edit data - I recently found a quick way to do some data editing for a table with filter added. That is from design...

Truncating data on a remote Server - Hello, I am trying to truncate data from one server to the other (remote server) [u][b]it works from query analyzer like this:[/b][/u] exec...

Performance issue on DB - Hi, We have a sql 2005 prod server,where one of the DB became very slow past 5 days. i have observer that...

Handling error message - Hi, while writing script I have to check that whether input value is correct or not and then display the...

Raid 10 - Hello. The average bytes/read is between 64k and 128k and the average bytes/write is between 32k and 64k. What is the best,...

SQL Disk Error - Dear Gurus, I am getting attached error of disk when installing SECOND INSTANCE of SQL 2008 R2 please help . http://tinypic.com/view.php?pic=2nuk85z&s=5 http://tinypic.com/r/14crv5u/5 http://tinypic.com/r/t057jr/5 Your kind...

SSIS in cluster - I have two nodes nodeA and nodeB on a sql server on a fail over clustered environment. Each of these...

Insert bulk failed due to a schema change of the target table. - Hello Expert. First time I used sqlbulkcopy its performance is impressive, however my application threw a SQL exception after processed about...

SQLSERVER Agent is not running- SQLSERVER 2008R2 - Hello: We have Windows 7 with SQLSERVER 2008R2 Express Edition 10.5 Installed. Database Engine and SQL Browser services are running perfect...

Shrink database while restoring - Can we shrink the database files while restoring from backup?

SQL Server 2008 : T-SQL (SS2K8)

Problem with FOR XML EXPLICIT - Hello everybody! I'm new to FOR XML... So here is my problem: I've got a query that returns the correct result regarding rows,...

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value - Im using this syntax to get a value but it gives me a error in visual studio: The conversion of a...

insert or update according the field value - Hi. I try to write a stored procedure. First, if record exists, it will look at recordtime. If recordtime is...

CALCULATE 2 years back data - how to calculate 2 years back data suppose today 07/15/2013 ,2 years back ,07/15/2011. how to calculate exact date?

Performance Tuning Large update statement--HELP - I have a 1.2 TB database in which I am updating a couple columns across 11 tables. T1 (F_name, L_Name, Address) t2...

Sum Negative Numbers - Hi All, I have a column GNLBalance with negative and positive balances. I want to add these numbers together when another...

SQL Server 2008 : SQL Server Newbies

Prev and Next Row Without RowNumber - Hello All, Can anyone help me with this one. here is the table sample. [code="sql"] --------------------------------------------------------------- CREATE TABLE [dbo].[Invoice_t]( [Cost_Center_code] [int] NOT NULL, [Payment_code] [int] NOT NULL, [INV_No]...

Not asking for any parameters in stored procedure - I am creating a stored procedure to send an email when the status field in a table changes from Pending...

SQL Server 2008 : Security (SS2K8)

What's the proper syntax for scripting a create login, associated with a group in AD? - In Active Directory, we've got a group named "Domain Users", and basically everyone is in that group. I want to...

SQL Server 2008 : SQL Server 2008 High Availability

Moving storage (SAN hp -> eva) with SQL Mirroring - We have a request come in to have the storage moved form HP--> EVA. The server that is part of...

Database Mirroring Or Log shipping?? - Hello all, I want to maintain another database only for Reports.what is the better ways for achieving this Database Mirroring...

SQL Server 2008 : SQL Server 2008 Administration

resource error 0x800706B9 on installing a sql2008 r2 instance - Afternoon All I was in the process of installing a SQL2008 R2 64-bit SE instance on a two node cluster...

SQL Server Crash Dump - Hi All, I have strange problem with my live cluster environment. I have a dev environment in which a procedure completes...

dear all, - can anyone please le tme know, in sql 2012 high avalability solution REPLICATION is deprecated or what? i got this...

Transaction Log Backup fails Exclusive access could not be obtained because the database is in use - I have an AM and a PM TransactionLog Backup. The AM runs fine the PM fails I get the following error. Date 07/15/2013 03:00:00...

how to find log shipped databases in given server - Hi all, I am really hard to finding how many databases are under logshipping in a given server. also that script...

Data Base File Swap - Hi All, We have a huge load scheduled every day during that time we have no access / data for end user...

Upgrade from Sql 2003 to Sql 2008 - Hi, We are planning to upgrade from Sql 2003 to Sql 2008 and I would like to know what needs to...

copy system database files - Dear All, I would like to have a backup of the SQL system databases (the files themselves) not the SQL backup....

Career : Events

SQL Saturday #230 - I visited SQL Saturday in Germany. It was awesome. I found something new for me, about Hadoop and Sentiment Analysis. You can...

Programming : Connecting

Install SQLServer 2000 in Windows Server 2008 - Dear All, Can we install SQLServer 2000 (Developer Edition) in Windows Server 2008 (Standard Edition)? Please advise/confirm. Thanks and Regards, Ravichandra.

Programming : General

Zip code --> Distance in google maps - Hi, is there a Google SQL service where I could connect, query for two zipcodes and retrieve the distance I...

Spliting a comma delimited parameter from Crystal - Hi there, I'm new to SQL and to Crystal reports. I know that to pass a multiple value parameter to a...

SQLServerCentral.com : Anything that is NOT about SQL!

New kid on the block - I came over here when the NNTP bridge for the MSDN forums stopped working, and I've only been here for...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

Reporting Services : Reporting Services

Printing in IE10 problems? - Anyone else crashing SSRS when trying to print in IE 10? Thanks

Two instance of Reporting Service - Correct me if I'm wrong but it's not possible to have to instance of Reporting Service under one SQL instance....

Database Design : Design Ideas and Questions

Is it Better to use Multiple DB than Single DB? - Hi In need to design an Enterprise level solution which will contain around 8 modules (such as Accounting, Human Resource, Inventory,...

Data Warehousing : Integration Services

Using SIS to move a file to a IP address - I have an SIS package running on a cloud server and I am trying to setup a connection to send...

SSIS Logging - start and end dates - We use a fairly detailed level of logging within our packages. It's configured for each package by selecting the SSIS / Logging...

Records Missing when SSIS dumping Data to Oracle Staging. - Hi All, I have got a situation where my SSIS package dumps Data from a SQl Server source to a Oracle...

Error - The Request Failed with HTTP status 407 Proxy Authentication required - Hallo, We have a SSIS job on SQL 2008 R2 which is calling a web service from a script component. When I...

Data Warehousing : Analysis Services

Process partition errors after cube migration - Hi, I'm trying to process a partition on a cube I've migrated from SQL2000 to SQL2008 R2 (both the DB...

Microsoft Access : Microsoft Access

Update Field to current logged in user ID with new record command button - Hello all, I have an access front end that uses a sql backend. We want to add a field in...

Article Discussions by Author : Discuss content posted by Roy Ernest

Problem Related to Serer Audit Specification - I have created a Server Audit and Server Audit Specification object . When i create,alter(Enable,Disable) or drop Server Audit i can...

To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2013 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com