Friday, May 31, 2013

[SQL Server] Efficiently Reuse Gaps in an Identity Column


Efficiently Reuse Gaps in an Identity Column




Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.



SQLTeam.com Articles via RSS


SQLTeam.com Weblog via RSS



.

sqlteam.com

Posted by at No comments:
Labels: ,

[SQL Server] Using REPLACE in an UPDATE statement


Using REPLACE in an UPDATE statement




Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.



SQLTeam.com Articles via RSS


SQLTeam.com Weblog via RSS



.

sqlteam.com

Posted by at No comments:
Labels: ,

[SQL Server] Handling SQL Server Errors


Handling SQL Server Errors




Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.



SQLTeam.com Articles via RSS


SQLTeam.com Weblog via RSS



.

sqlteam.com

Posted by at No comments:
Labels: ,

[SQL Server] Using Dynamic SQL in Stored Procedures


Using Dynamic SQL in Stored Procedures




Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.



SQLTeam.com Articles via RSS


SQLTeam.com Weblog via RSS



.

sqlteam.com

Posted by at No comments:
Labels: ,

[Articles] The Bicentennial

[Articles] The Bicentennial

The Bicentennial

Posted: 30 May 2013 11:00 PM PDT

Tomorrow is the bicentennial SQL Saturday event. SQL Saturday #200 takes place in Philadelphia, and Steve Jones has some thoughts as he travels to the City of Brotherly Love.

sqldbabundle 'Disturbing Development'
Grant Fritchey & the DBA Team present the latest installment of the Top 5 hard-earned lessons of a DBA – read it now
Posted by at No comments:

[MS SQL Server] Transactional Push Replication - Overhead of having Distributor on the Publisher

[MS SQL Server] Transactional Push Replication - Overhead of having Distributor on the Publisher

Transactional Push Replication - Overhead of having Distributor on the Publisher

Posted: 31 May 2013 12:21 AM PDT

I've recently set up Transactional Replication with Push subscriptions. At the moment the distributor is running on the publisher server. I've been getting vague reports of "it's going slow since we put replication on". I'm aware that running the distributor on the publisher server has an overhead, but I can't find any specific information on what that overhead is. I'd imagine that it's an amount of I/O as it writes the transactions into the distribution database, plus a bit of CPU? Weirdly, the reports are that the published database is running slowly, but other databases on the server (and on the same disk) are running fine. I'm a little skeptical to honest as I'd have thought extra I/O or CPU load would affect the whole server, rather than one specific database.If it turns out that it [i]is[/i] causing issues, what are my options?- I've ruled out Pull subscriptions because we're replicating to SQL Server Express, which doesn't have SQL Server Agent to run the jobs.- It would be a very hard sell to convince the customer to buy another server to run the distributor on.- Would it be worth adding a new disk to put the distribution database on, but still running the distributor on the publisher?I realise that this post is lacking specific information, but at this stage I'm just looking at what my options might be. I'm a developer who does a bit of DBA-ing and I'm bit new to replication, so I understand the basics but the minutiae are a bit of a mystery to me! Any help you could provide would be most appreciated. Thanks in advance

Database just says restoring...for last 20 hours?!

Posted: 13 Jan 2010 12:00 AM PST

Hello,I've got a SQL Server 2005 server that I have remote access onto as well as using SSMS.I've got a SQL Server 2008 server that I don't have remote access onto and only can use SSMS.I need to move a database from the old 2005 box to the new 2008 box.I did a backup to our Backup server (across network). That went OK.Then I needed to get the backup to the new server, but I can't just remote it and copy from the backup server.I tried a restore backup with move. That seemed to work. It said it was restoring. That was 20 hours ago. The database is a small database. 1) How do I stop it? I tried taking it offline. It gave me an error message saying that it was busy restoring the database.2) How do you restore a database through TSQL from a network server? I thought the restore "WITH MOVE" was the right way.Thanks!

DB Randomly sets in Single User Mode

Posted: 30 May 2013 10:37 PM PDT

I have a database that on at least 2 occasions randomly went into single user mode.It was not immediately after a backup - I don't see any alter database statements and I find no comments in syscommentsIs there any idea why it does/did this and where else I can look? Please advise

VLFs sizes

Posted: 30 May 2013 09:42 AM PDT

Hi All,I want to see someone's explanation if there is a performance loss (and why in case of) when some VLFs are small and some are big, in a transaction log.For example a database had had small growths of 128MB, and each VLF with 16MB; and then a change of 4608MB is set for the growth, and each new VLF is now 288MB.I know how to make the VLFs all equal. It will be good posting some links where this issue is discussed, too.Thank you,IgorMi

DB Backup Maintenance Plan Won't Execute

Posted: 31 Jul 2012 08:22 AM PDT

I've created a back up plan on one DB using the Maintenance Plans Wizard and the regular way (New Maintenance Plans). Everything looks to be setup correctly but when I try to execute the plan to see if it'll run properly, the plan fails to execute giving me an "Execution failed" message. Looking at the SQL Server Agent job history it seems like my SQL Server can't determine if my Windows NT group/User ID has the appropriate server access. I'm able to do basically everything I want to inside the server but execute the maintenance plan. Does any body know how to change my server access so I can get my maintenance plans running?

SQL 2008 Audit doesn't show parameters

Posted: 05 Sep 2009 03:54 AM PDT

I need to audit all accesses, updates, inserts, deletes, executes, etc. done to specific tables in our database. I upgraded my SQL Server to 2008 so I could use the new Audit functionality. This works great and shows me everything I need and who accessed it. The problem is I don't know what the query contained. Let me give some examples:If I run a query from the Management studio it gives me the following:Query that was run: Select * From Customer Where ID = '123'Audit shows this: Select * From Customer Where ID = '123' This is what I would expect all the time. But if I add in a parameter it gives the following:Query that was run: Declare @id varchar(255) Set @id = '123' Select * From Customer Where ID = @idAudit show this: Select * From Customer Where ID = @idI would like to be able to see what the parameter was in case I need to investigate something.Does anyone have any suggestions? Our application sends in thousands of stored procedures with parameters and how it currently works won't help.Thanks in advance!JN
Posted by at No comments:

[SQL 2012] Log file continues to grow after backup (full recovery mode)

[SQL 2012] Log file continues to grow after backup (full recovery mode)

Log file continues to grow after backup (full recovery mode)

Posted: 31 May 2013 02:07 AM PDT

I recently upgraded to SQL Server 2012 from SQL Server 2005. The database is in full recovery mode and I take full backups nightly and transaction log backups hourly. However, the transaction log is not being shrunk after backup and continues to grow daily. Switching the database to simple recovery and running DBCC Shrinkfile will reduce the file size. However, the issue continues after the database is back in full recovery and log backups are taken. Has anyone experienced this issue?

Questions on installing a PowerPivot for Sharepoint instance in a cluster

Posted: 31 May 2013 02:25 AM PDT

Hello all,Still reading through documentation; however have some quick questions (for now) on how to properly setup a Sharepoint instance for PowerPivot. Short version is that we have a multi-node, multi-instance cluster in which we host several different database instances. One in particular is purely for our Enterprise SharePoint. Now that we're moving to SQL 2012 and SharePoint 2013, I'm a bit confused on a couple of key pieces.1) Does the SSAS instance reside with the database instance, or the Sharepoint application server?2) If using SSRS integrated mode on the database instance, I can still have the SSRS application end live on it's own server correct?3) Heading to lunch, will come back to this as I'm drawing a blank at the moment.Thank you

Update query with right([xxx],2) changes varchar to smallint

Posted: 31 May 2013 12:58 AM PDT

This shouldn't be that hard.My situation. Working with addresses on a map. Some of the addresses will be the full length. 4 or 5 characters that are numbers but reside in a varchar column. One column contains the number of characters(numbers) I want presented. For example, think of a row of townhouses. I want both ends to show the full address i.e. 9900 to 9908. The inner addresses will only have two i.e. 02, 04, 06, 08. My query will show apartment numbers, but that isn't a concern.My query:UPDATE [Parcels].[tbl_Address_Basic] SET [FONT_TEXT] = CASE WHEN [FONT_NUM] = 9 THEN [APPT_NUM] WHEN [FONT_NUM] = 4 or [FONT_NUM] = 5 THEN [ST_NUM] WHEN [FONT_NUM] = 2 THEN RIGHT([ST_NUM_TXT], 2) ENDProblem: After update, the column only returns 2,4,6,8 instead of 02,04,06,08. 9900 would return 0. ST_NUM is small int and I thought that was the problem, so I created ST_NUM_TXT which is varchar(10). Same result. I've tried casting and same thing. I tried right('0' + [ST_NUM_TXT], 2) and same thing. I tried ('a' + [ST_NUM_TXT], 2). I know, I know, but I was out of ideas. I received the error: "Conversion failed when converting the varchar value 'a00' to data type smallint". I've tried casting on the inside and outside. I'm out of ideas. BTW, FONT_TEXT is varchar(10). Any help would be appreciated.Monte

Help on performance issues using SQL Server 2012

Posted: 30 May 2013 10:03 PM PDT

Hi,I need help on performance issues with our new database server using SQL Server 2012. A migration was done over the weekend. And we moved several databases to a new data center. Now the ERP system is slow and users cannot get their work done.I created a reindex and reorganize scripts, we were using a job but that was taking too long and failing. I ran a fragmentation analysis and using a threshold of 20% for reindexing and everything else reorganize. This enable the job to complete. However the performance is still awful. The database server shares multiple databases, one of the recommendations I've made was to have a dedicated server for the ERP system.There is an audit trail file that hasn't been purged in about 10 years. Could this be the reason for this bad performance. It's affecting all systems pointing to this database server.I didn't have a say in this decision - Now the business is ticked.Any help would be greatly appreciated. And if none if this works we need to migrate back to SQL Server 2005.

Open SSMS 2012 using RUN window

Posted: 30 May 2013 09:52 PM PDT

Hi,Just want to know what shortcut can we use to open SSMS 2012 as,we are using RUN(window + R) --> SSMS for 2K8

SQL Query autoformating

Posted: 30 May 2013 03:42 PM PDT

Hi all, There are many third party tools available in market to format your SQL queries. If you are using any of these tools then can you please share your experience. Which tool is better?

Parsing huge XML file into a database table using sql server

Posted: 30 May 2013 05:39 PM PDT

I tried to parse a huge xml file using sql server (OPENROWSET) into a database table, but got an error that says duplicate attributes (author). The author column varies for each article, ranging from one author to upto six authors. The columns I am really interested in are author, title and journal. Below is a snippet of the xml data. Any suggestions will be highly appreciated. Thanks in advance.<?xml version="1.0" encoding="ISO-8859-1"?><!DOCTYPE dblp SYSTEM "dblp.dtd"><dblp><article mdate="2002-01-03" key="persons/CoddD74"><author>E. F. Codd</author><author>C. J. Date</author><title>Interactive Support for Non-Programmers: The Relational and Network Approaches.</title><journal>IBM Research Report, San Jose, California</journal><volume>RJ1400</volume><month>June</month><year>1974</year></article> </dblp>

Kerberos on SQL Server 2012 Reporting Services Sharepoint integrated

Posted: 26 Apr 2012 02:07 AM PDT

Hi.I have sharepoint 2010 installed and SQL Server 2012. With SQL 2012, if using reporting services in sharepoint integrated mode there is no longer a Reporting Services Service, instead it is fully integrated into sharepoint.This unfortunately leaves me with no idea how to enable kerberos for reporting services when using SSRS 2012 in Sharepoint integrated mode.Any info i can find is for SSRS 2008 and Sharepont, not SSRS 2012 and sharepont.Can anyone advise what needs to be done here to ensure im using kerberos?
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog