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

Why is my transaction log full?

Gail Shaw from SQLServerCentral.com

Why does the transaction log fill up? There are a variety of reasons, and MVP Gail Shaw walks you through the various things to check. More »

SQL in the City - Boston 2012

Press Release from SQLServerCentral.com

A free day of training in Boston on Oct 8, 2012. Come join Grant Fritchey, Steve Jones and more to talk about SQL Server and how you can work more efficiently. More »

AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups

Additional Articles from Microsoft MSDN

SQL Server 2012 AlwaysOn Availability Groups provides a unified high availability and disaster recovery (HADR) solution that improves upon legacy functionality previously found across disparate features. Prior to SQL Server 2012, several customers used database mirroring to provide local high availability within a data center, and log shipping for disaster recovery across a remote data center. With SQL Server 2012, this common design pattern can be replaced with an architecture that uses availability groups for both high availability and disaster recovery. This paper details the key topology requirements of this specific design pattern, including quorum configuration considerations, steps required to build the environment, and a workflow that shows how to handle a disaster recovery event in the new topology. More »

From the SQLServerCentral Blogs - SQL Server Guest User – Still a Serious Security Threat

Basit Aalishan Masood-Al-Farooq from SQLServerCentral.com

One of the security recommendation inside SQL Server Security Best Practice white paper for guest user is that, to disable... More »

From the SQLServerCentral Blogs - SQL Server 2012 and SharePoint 2013: Installing on a Virtual Machine

With the preview of SharePoint 2013 now available, I have updated my post SQL Server 2012: Installing on a Virtual Machine to... More »

This editorial was originally published on Nov 19, 2007. It is being republished as Steve is on vacation.

When I started in IT things were much simpler. We had smaller data sets, but hardware was larger. There just wasn't a good way to transfer large amounts of data on 3 1/2" floppy drives. I remember CDs coming into being and CD writers became a concern, but since few people had them and we knew who they were, it wasn't a big problem. The zip drives created a cause for concern briefly with their 100MB capacity, but again, they were somewhat bulky and easy to spot.

However with the growing sizes of flash drives, storage in cell phones, and the monstrous capacity of iPods and other music players, it seems that data has little chance of being contained by IT within the walls of the organization.

I saw an interesting discussion about managing all these technology devices and the problems that come with so much storage being in reach for so many people.

So we're data people, we get beat up to ensure our SOX procedures work well, and we're in the trenches. With that in mind, the poll this week is...

Does it make sense to ban personal storage devices?

I know it's not practical to actually try and prevent the iPods and other devices from coming into the building. You for sure cannot take away all the cell phones from people. But does it make sense to prevent these devices from connecting to your network? Ban USB and Bluetooth; don't purchase rewriteable drives except for admins who can be monitored. Some other ideas?

That might not even work. Recently a report surfaced about some executives in Korea that stole nearly $2billion worth of trade secrets with USB drives and taking them to a new company.

Think about the past experiences we''ve had. Suppose you''d locked down floppy drives and CD burners a decade ago. All of a sudden MP3 players appear and get mounted as removable drives. It''s a new twist you hadn''t considered, so you shut down all the serial, parallel, and USB ports. You get a new laptop and realize Bluetooth is now available and works just as well and your controls have been circumvented again. What do you do now? It's a tough balance to strike. Are you trying to be a control freak and make it an hostile work environment or do you trust your employees and deal with the occasional problems that come with them?

I'm not sure what the best solution is, but I'd venture to guess that banning the technologies won''t work. Someone will always come up with a new way to get around your controls, and more importantly, you won''t be as vigilant if the controls “appear” to work.

I'd adopt the Counterpane approach to security, which is what security expert Bruce Schneier believes in. Assume you''ll get compromised and attacked and put systems in place to detect and respond to issues rather than trying to prevent all attacks.

Steve Jones

The Voice of the DBA Podcasts

The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). We've upped the quality a little on the Quicktime files, so if it's better from your side, let us know. Comments are definitely appreciated and wanted, and you can get feeds from there.

The RSS Feed: or now on iTunes!

• Video Podcast - 22.5MB Quicktime
• Video Podcast - 9.5MB WMV
• Audio Podcast - 4.1MB MP3

Today'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.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

Steve Jones from SQLServerCentral.com

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

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

Today's Question:

DECLARE @tmp TABLE (SlNo INT IDENTITY,Name VARCHAR(200))

INSERT INTO @tmp(Name) Values('SCREW')
INSERT INTO @tmp(Name) Values('HAMMER')
INSERT INTO @tmp(Name) Values('SAW')

DELETE FROM @tmp WHERE SlNo = 2

SET IDENTITY_INSERT @tmp ON

INSERT INTO @tmp(SlNO,Name) Values(2,'SHOVEL')

SELECT * FROM @tmp

What will be the Result ?

By Unknown

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

This question is worth 1 point. We keep track of your score to give you bragging rights against your peers.

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

In SQL Server 2012 RTM, which of the following are valid index types?

Note that some index types may require additional attributes to be specified; this does not invalidate them for the context of this question. Only index types that include illegal attribute combinations or non existing attributes should be discarded. Also note that this question is not about the exact spelling of the various options.

This is a question with 4 correct answers. Please select all the options that are correct.

By Hugo Kornelis

Answer:

• Fulltext with statistical semantics
• Filtered nonclustered
• Nonclustered on a view
• Columnstore on 260 columns

Explanation: The functionality for fulltext indexes has been extended in SQL Server 2012 to allow semantic search; this is enabled by adding the optional STATISTICAL_SEMANTICS keyword to the index. SQL Server allows indexes to be filtered by adding a WHERE clause to the index definition. This is only valid for nonclustered indexes. SQL Server allows additional columns to be included at the leaf level pages only, but this option is also available only for nonclustered indexes (since clustered indexes always include all columns in the leaf level pages anyway). Bitmap indexes are offered by other database vendors, but not by SQL Server. While it is not possible to create a nonclustered index on a view directly, it is possible to do so after first creating a unique clustered index on that view. Columnstore indexes can be defined on up to 1,024 columns.

References: CREATE FULLTEXT INDEX: http://msdn.microsoft.com/en-us/library/ms187317.aspx
CREATE INDEX: http://msdn.microsoft.com/en-us/library/ms188783.aspx
Creating Indexed Views: http://msdn.microsoft.com/en-us/library/ms191432.aspx
CREATE COLUMNSTORE INDEX: http://msdn.microsoft.com/en-us/library/gg492153.aspx

» Discuss this question and answer on the forums

Job Running time/Interval precentage

tomer lev from SQLServerCentral.com

the script has been developed on SQL server 2005 and gives the ability to find out from all active jobs the precentage of running time divided to job interval in the last 24 hours. More »

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

SQL Server 2005 : Administering

Performing database backups while users are online? - Greetings! Just a quick question this time. The majority of our users finish work at 5.30 so I suggested to my superior...

Researching Deadlocks - Hi all: I've seen that you can use trace flags 1204 and 1222 to return information to the error log about...

Temp db log file is growing . - tempdblog file is growing to 5 gb. I dont have more space on the drive. want a immediate solution . without restarting...

Need your advice on an automatica job - Hello, I have a project, it's an asp.net application using SQL 2005 as back end (will soon be upgraded to 2008...

xp_delete_file not deleting files on share drive - Hello, xp_delete_file not deleting files on share drive. is there any permission to be given?

Changing FileGroup for File Name - Hi, How can I change a file gorup for a specific file name? The issue that I'm having is that I already...

Mirroring and SQL logins... - Hi, If I run "select * from sys.sql_logins" on my principal and mirrored servers it gives me the exact same results...

SQL Server 2005 : Backups

Backup in suspended state - Hi, In one of my production servers, the backup is going to suspended state with "ASYNC IO completion", and it is...

SQL Server 2005 : Business Intelligence

ssis null valus - hi friends i have small doutb in ssis plz tell me how to solve this issue how to handle nulls data...

How to trigger SSIS from SSRS? - I want to trigger SSIS package from SSRS after inputting some fields. Thanks in Advance. Thanks Surendra

SQL Server 2005 : Data Corruption

SQL Server detected a logical consistency-based I/O error : SQL 2005 - Getting this: Msg 926, Level 14, State 1, Line 2 Database 'DM_m000003_m000002_qdb' cannot be opened. It has been marked SUSPECT by...

SQL Server 2005 : SQL Server 2005 General Discussion

SQL 2005 Mirroring in Permanent Disconnected State - We have SQL Server Mirroring set up on a database between two Servers in different parts of the country connected...

SQL Server 2005 : SQL Server 2005 Security

Column level audit ... - I need to trace the user log against my database tables. Most of the tables have the customer’s sensitive data....

SQL Server 2005 : SQL Server 2005 Integration Services

Microsoft excel version - my excel file has xlsx version. and i am using visual studio 2005. it gives me error at excel source connection. what...

derived column - in my excel sheet i have 1 column which contains name like shah, anki S. dogula nanacy baskin brian B waive, FRANCISCO p. le,...

package - hi, i have 1 excel file which has like id, name,address0, address1, address3 now i need to make package that take data...

Avoiding Truncation? - Hi Friends, My project is under SSIS 2008, SQL 2008. there is a column from OLEDB source coming as DT_NTEXT....

Render SSRS Report from SSIs package - I am populating Dimension table "D_RepLicense" and for this dimension I am fetching RepID from "D_Rep" dimension by joining RepCode...

SQL Server 2005 : T-SQL (SS2K5)

How to write Transpose in Oracle Please Help - sample date ----------- source ------ ID-----NAME-----------ADD1--------ADD2---------ADD3------ADD4-------ADD5---------ADD6 1 ------ ABC 12B 13B 14B 15B 16B 17B 2 ------ BBC 10V VCF BB 3------ ADD 30 TARGET ----- id ------ NAME------ADD 1-------ABC---

SQL Server 7,2000 : Administration

database connection for primavera p6 - sql server with pmdb$primavera database is running ok ,but connection with project management module is not establishing. not trusted server...

SQL Server 7,2000 : Data Corruption

REPAIR_ALLOW_DATA_LOSS not resolving the allocation error - hello, this is on sql server 2005. it is for a new client. this may be the first time dbcc checkdb...

SQL Server 7,2000 : General

SQL 2000 Server DTS Job is failing unusually - Hi All, One of my DTS job scheduled for daily run, the job having collection of packages like task1, task2 etc. task2...

SQL Server 7,2000 : SQL Server Newbies

Simpler way to join all unique columns for a join? - Here's the Union that I clearly can't do.... I have 30 tables of inspections, deliniated by *Inspection. I have included...

SQL Server 7,2000 : Replication

transactional replication subscription deleted automatically SQL SERVER 2000 - I have replicated(transactional replication) databases in sql server2000. it had some scheduled time for doing replication. but after few days...

SQL Server 7,2000 : T-SQL

T-SQL 2000 to 2008 - Hi,. Hi I am having around 200-250 reports that are developed in SQL 2000 and i have used *= , =* many of places....

SQL Server 7,2000 : SQL Server Agent

Creat one sql job on 4 different environments same time in one script - --I need to know codes for GO TO NEXT STEP, and need to replace with script below --also I want to...

SQL Server 2008 : SQL Server 2008 - General

execute a procedure in another one - hi i am trying unsuccessfully to execute the following procedure in another one 1-[code="SQL"] USE [RECLACSR-DB] GO /****** Object: StoredProcedure [dbo].[CreerFile] Script...

its ages since i wrote a trigger - Ive written this one [quote]create trigger dbo.tri_Cancelled on Orders for update as if Update(Canc) begin if (Select Canc from Inserted) <> 0 Begin UPdate Orders set CancDate = getDate()...

Best way to truncate a log in simple mode ? - Hi, I have a 60Gb log file i need to truncate on a DB running in simple mode - what is the...

Querying EAV data - Friends, I am using a third partry tool whose database is modeled using Entity-Attribute-Value model. What according to you would be...

Trigger to split one column and insert into another table - Hi, I need a trigger on below condition, am having a table called "[b]Tracker[/b]", [b]msg_text [/b]is a column in the tracker table. msg_text...

Push SQL 2008 data to MYSQL via SSIS - Hi all I am trying to use SSIS to push data to MYSQL. I can do this using a ADO .NET connection...

Merge and combine two tables - I need to merge/combine the following two tables (tabl1 & table2). Results are in #Mergedtables . Value data on both tables are...

Partitioning : SPLIT seems to use the wrong filegroup ? - Hey folks I'm trying to use SPLIT on a partitioned table but the placement of the boundaries on filegroups end up...

Capture logical reads for a specific table for specified duration - Hi, For a given timeframe, is there anyway to capture what are the top 10 tables by logical reads, logical writes...

Odd behaviour from computed columns in a left outer join - I have the following query: [code="sql"] SELECT CE.ID , CA.CustomerID, CA.PostalCode FROM DBXN.CommunicationEntry CE LEFT OUTER JOIN ( SELECT A.ID , CA.CustomerID , A.PostalCode, A.EnglishAddressLine1 FROM DBXN.Customer_Address AS...

Setup log shipping, can't view Reports on secondary DB - I setup Microsoft standard native log shipping for a small database. When I right click > Reports, I get an...

Replication failing with error "Column name or number of supplied values does not match table definition." - Hi all, I have created a pull transactional replication where the replication was running fine until the following error occured for...

SQL Trace - simple SELECT shows writes - Hi there! As the subject says, when I read a trace file and the SQL:BatchCompleted event, the Writes column contains a...

all about BI,sql - Hello, I am new to this forum, and really appreciate the responsiveness from my first post. Here’s my question : I...

remove identity property - hello all. I want to insert into identity column and i want to first remove identity property and insert value and...

cannot connect to named instance thru SSMS on developers desktop - Hi Experts, I got a scenario where one of the developer cannot connect to named instance, but able to connect to...

How to Generate Required xml format using t-sql - <code> <timevalue value="20120101"/> <value xsi:type="sa" value="10"/> </code>

How to avoid this T-SQL statement affecting my TempDB? - [code="sql"]WITH CTE AS (SELECT DISTINCT LTRIM(RTRIM(P.IncidentIDRef)) AS MODIDREF, P.JurisID, P.EntDttm AS CREATEDDTTM, P.DIDCU AS CREATEDPFIDREF, 'INC' AS MODCODE, 'C' RECTYPE,...

Weird Requirement... Multiple Left Joins? Am I missing something? - Ok, so I think I should know the answer to this but I'm getting nothing but what looks like a...

Derived column - i have column value something like below. {784A4579-8689-438E-ADAA-9DCBC8A88AE7} I just need "784A4579-8689-438E-ADAA-9DCBC8A88AE7" i.e value between {} can any one help me with this? thanks!!

Running SSRS report from SSIS package - I have an SSIS package. It updates a table in SQL Server 2005 database. There is another SSRS report that...

SQL Server 2008 : T-SQL (SS2K8)

Getting long time (Need perform sp) - Sp is getting more time(more than 7m) to generate report :w00t: .Output has more than 21000 records.Are there any good...

Unique runs - I am looking for a query that will produce a list of unique runs for the following data, where EvDate...

Select table names from queries - I have a table with 1 column (nvarchar(max)) that holds a list of distinct queries. Does anyone have a query...

Adding Seconds to Time field - I have a field that contains a time (i.e. 16:40:27) and I have a field of "SecondsAtStatus" (i.e. 734). I need...

format for phone number - hi i need to craete table which has phone number field, format (345) 234 - 2345 what is the best data type i can...

Stored procedure in Where clause - Hi, [b]below is the query to display the project_name where project_name matches the condition.[/b] select project_name from Groups where project_name like '%20%Catalog%' [b]below is...

will there be a table lock situation while deleting records from a partitioned table ? - HI Consider the below scenario: I Have a table with 5 partitions based on the org_id. In other words : Table A Partition org_id 1...

help with SQLCMD - Hi , I am trying to use SQLCMD to run a SQL Script. The script is of type. Begin tran Delete ..... Insert ..... Commit...

Best Practice : coalesce vs. isnull? - Just wondering, I like to use ISNULL(column_name, 0) while my friend like to use COALESCE(column_name, 0). We both try using ISNULL...

SQL Server 2008 : SQL Server Newbies

how to manage to get the range Id of a specific number of minutes - Hi I can imagine that the subject is not clear at all... So let's give you an example. I have a table...

Decimal Hours Duration to HH:MM - Hi, Anyone got a simple method for converting decimal time duration to HH:MM. For example: 1.5 to 1:30 30.75 to 30:45 95.25 to 95:15 etc Many thanks...

changing Agent jobs notification via msdb? - Hi all, Id like to make sure all my agent jobs update the event log if they error. If i...

SQL server 2008 r2 expired? how reinstall? - Hello! I have problem, because I have SQL Server 2008 R2, and we have to install 180 day trial, and now...

Help needed in converting an int datatype to MM/DD/YYYY and converting another int to HH:MM PM - When running the script below I noticed that the two result fields are of datatype int. I would like to convert...

SQL Server 2008 : Security (SS2K8)

Execute Stored procedure and SSIS Packages in different servers - Hi All, I have to execute some stored procedure and SSIS packages (It copies data from one to table to another...

Another linked server login problem - hi everyone, i need to consult this issue am having with linked server. i setup a linked server from server1 to...

Orphaned Users - Hi All I'm planning on backing up and restoring +- 20 Databases from one SQL Server to another I've been reading about orphaned...

SQL Server 2008 : SQL Server 2008 High Availability

safely remove rowguid column? - Hi, Recently database was migrated to SQL 2K8R2 from SQL 2K. Already configured replication in SQL2K, replication was removed in SQL 2K...

Regarding clustering - Hi all, Can you rename a virtual SQL server name if you input the name incorrectly when you installed it....

Replication Scenario - Hi all, Am a newbie into SQL 2008 R2 Replication. My requirement is as follows. We have differrent branches in differrent cities and...

Log shipping secondary not showing new results from primary - Hi all, Just set up log shipping. I get no errors from the agent jobs and can see the files...

SQL Server 2008 : SQL Server 2008 Administration

Changing the clollation settings for MASTER DB - Please tell me how to change the database collation settings for master DB

SQL Server 2008 Profiler Query Replay Issue - Dear All, We are upgrading our products' database from 2005 with 80 mode to 2008 with 100 mode. To verify the...

Network IO Waits threshold... - Can anyone guide me on how much threshold sholud I set for Network IO Waits... I am using Idera SQL Diagnostic...

TCP/IP Error while restoring a Database through network - Hi, I'm unable to restore a database through network. Server A: Host of backup copy.Hosted in 8 node Cluster 1. Server...

Adding Secondary Data Files (ndf) - I have a 310GB mdf for my warehouse database. I have just added three more data files to it in...

Excessive recompiles - I see several entries of "UPDATE MSDB.DBO.SYSJOBSCHEDULES SET NEXT_RUN_DATE = ...." in cache. You can find those using this query: select * from sys.dm_exec_cached_plans a cross...

Join making conflict.... - SELECT @MSGOUT = @MSGOUT + ' <tr> <td>' + ss.ServerName +'</td> <td>' + ss.DBName +'</td> <td>' + Occurrences +'</td> </tr> ' FROM DBINVENTORY ss INNER JOIN (SELECT ServerName,DBname, cast(COUNT(dbname) as varchar(5))AS...

what is CPU ? Physical or logical ? - When SQL Server runs on a computer with more than one microprocessor or CPU, it detects the best degree of...

Add multiple users and give permissions to many databases - Hi everyone. I have a list of logins that I need to add to SQL Server 2008. We need to give...

Renamed AD users' accounts, now some SQL Servers can't get info - We changed some users AD accounts to a new name (samaccountname, displayname, logon, etc..) On one SQL server (2 instances), we...

Career : Certification

Passed 70-448! - I passed the 70-448 exam for the [url=http://www.microsoft.com/learning/en/us/exams/70-448.mspx]MCTS: Microsoft SQL Server 2008 Business Intelligence Development and Maintenance [/url]certification this weekend....

SQLServerCentral.com : Anything that is NOT about SQL!

Is it OK to ask salary range before applying? - Hi all, I came across a Jr. DBA position with no salary attached. A google search of the company didn't turn...

Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You...

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

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 2005 Development

sql server 2005 - hello, i want to change my serversql from case-sensitive to case non sensitive,someone can help me please. thank you in advance.

Call SSRS package from Stored Procedure or From SSIS? - Hello, Can anyone help me in calling SSRS package from Stored Procedure or From SSIS? We need to execute the SSRS...

Database Design : Design Ideas and Questions

Avoiding the Deep model (Key, value) - I am currently working with a group of folks to design a data model. Each record the main table in...

Data Warehousing : Integration Services

How to use .xlsb file in SSIS 2005 - Hi, I have .xlsb file. It has been used as a source and to insert the data to staging table using...

Potential loss of data for date column - Hi, I am using excel file as a source in SSIS 2005. Loading all the data from excel to staging table... Staging table...

Derived Column Error - I'm trying to "change" the data of a specific column during a SSIS process. I have a text file, that...

FINDSTRING Function - Hi All, We are using FINDSTRING Function in one of Data flow Derived Column (Component), It work Fine for Symbols like...

Microsoft Access : Microsoft Access

Access 2007 front end to SS2K8 problem - My app includes 1) SS2K8 database on the back end 2) Access 2007 front end NOT LINKED, data flows through ADO...