|
|||||||||||||||||
In this issue |
|||||||||||||||||
|
|||||||||||||||||
In This Issue |
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
From the SQLServerCentral Blogs -
|
|||||||||||||||||
|
|
|||||||||||||||||
Editorial - Fix the Little Things |
|||||||||||||||||
But I'm somewhat amazed how many fragmentation questions, posts, articles, and more that I still see on a regular basis. Shouldn't this be a low level feature of SQL Server that just works? In 2008, or I guess SQL 11 now, should there be any reason for a DBA to monitor this and write custom code to ensure that it's fixed on a regular basis? Should this not be something built into the system? I dream about the day of right clicking a table and choosing "defragment." This wonderful built in utility would then return my table to it's initial fill factor and clean up all the allocations of extents to ensure that they were contiguous. It would have options I could pull up that would set the minimum and maximum levels of fragmentation I accept, based on server defaults of course, and it would ensure that my tables were kept within those limits. Sure it would be a background process, it would consume CPU cycles and require a good amount of disk space, but those things are relatively cheap. Especially when compared with the cost of time for a DBA to manage this process. Even a few hours a year is too much time wasted by DBAs on a task like this. It's not sexy, but there are a lot of DBAs out there that would really appreciate it. As much as I know large sales dominate the market and this isn't something that reps at Microsoft can point to, it's the little guys, those DBAs in lots of 1 and 2 person shops, 10 and 20 instance companies, that make the recommendation to upgrade. And if Microsoft spent some time cleaning up little features, making things work better, I bet there would be a lot of recommendations to change versions. And, Microsoft, if you deliver small things that work well, along with the big, sexy features that flake or that we don't understand, you might be surprised how many of us would be looking to upgrade before Service Pack 1. Steve Jones » 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 |
|||||||||||||||||
|
|
|||||||||||||||||
Question of the Day |
|||||||||||||||||
|
Today's Question: Executing the code below, will the SELECT return a value of 1 or will an error message be generated? CREATE TABLE #Test (Col1 int) INSERT INTO #TEST VALUES (1) SELECT * FROM #TEST WTH(NOLOCK) Think you know the answer? Click here, and find out if you are right. This question is worth 1 point in this category: T-SQL. 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. |
|||||||||||||||||
Pro SQL Server 2012 PracticesExpert SQL Server Practices 2012 Edition is an anthology of high-end wisdom from a group of accomplished database administrators who are quietly but relentlessly pushing the performance and feature envelope of Microsoft SQL Server 2012. With an emphasis upon performance—but also branching into release management, auditing, and other issues—the book helps you deliver the most value for your company’s investment in Microsoft’s flagship database system. |
|||||||||||||||||
|
|
|||||||||||||||||
Yesterday's Question of the Day |
|||||||||||||||||
|
This question uses SQL Server 2012. The user defined error message 55555 does NOT exist in sys.messages. I have the following table. CREATE TABLE dbo.Test (ID INT PRIMARY KEY); --I then execute the following TSQL statement BEGIN TRY INSERT dbo.Test(ID) VALUES(1) INSERT dbo.Test(ID) VALUES(1); END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS Msg#, ERROR_SEVERITY() AS Level, ERROR_STATE() AS State,ERROR_MESSAGE(); THROW 55555,'why oh why do you do this',101; END CATCH /* The select statement returns: (actual message returned shortened due to lack of space to display full length in this QDD creation form ) Msg# Level State Error Message 2627 14 1 Violation of PRIMARY KEY constraint The questions are: (select 3 answers) a. What error level does THROW return ? b. What error state does THROW return ? c. What error message does THROW return ?
Answer:
Explanation: According to http://msdn.microsoft.com/en-us/library/ee677615%28SQL.110%29.aspx. The error_number parameter does not have to be defined in sys.messages. There is no severity parameter. The exception severity is always set to 16. |
|||||||||||||||||
SQL Server 2012 Query Performance Tuning SQL Server 2012 Query Performance Tuning leads you through understanding the causes of poor performance, how to identify them, and how to fix them. You’ll learn to be proactive in establishing performance baselines using tools like Performance Monitor and Extended Events. You’ll learn to recognize bottlenecks and defuse them before the phone rings. You’ll learn some quick solutions too, but emphasis is on designing for performance and getting it right, and upon heading off trouble before it occurs. Delight your users. Silence that ringing phone. Put the principles and lessons from SQL Server 2012 Query Performance Tuning into practice today. Get your copy from Amazon today. |
|||||||||||||||||
|
|
|||||||||||||||||
Featured Script |
|||||||||||||||||
Converting to datetime a char field with several possible date formatsConverting to datetime a char field having several possible date formats 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 : AdministeringAm I running sql2012 SP1? - Hi, This sounds strange, but I have evidences from either side. My SSMS Object Explorer reads SQL Server 11.0.2100.60, same... Jobs logged in the "Job Activity Monitor" but not in the "View History" - SQL Server 2005 Enterprise Ed, Hi guys, I have the following issue: There are some jobs running successfully. The execution is reported in... SQL Server 2005 SP4 - Hi I have few SQL 2005 with SP4.What is the mandatory Cumulative update or hotfix need to be applied Akkare DB in suspect mode - What internally happens if db goes in suspect mode ? SQL Server 2005 : Business IntelligenceProblem with SSIS job running in SQL Agent - I'm new in BI and technology in general so bear with me. I completed a lot of work in our... How to insert new record for a user having multiple records by performing lookup? - Hi, I have one table called Student. The source for loading the table is StudentHistory. My Scenario is i have a single... Sql Server Reporting services error "Operation is not valid due to the current state of the object" - Hi, Am getting error like "Operation is not valid due to the current state of the object" when i pass the... SQL Server 2005 : SQL Server 2005 General Discussionlinked server to transfer info from mssql to mysql - I made a linked server (as descriped) but I get the following error, Msg 7399, Level 16, State 1, Line... What Do You Call This Relationship ? - I have a situation with items that are related, but not really in a heirarchy ... more like parent to parent.... Trouble Importing CSV. "Truncation" despite Varchar(max) - I'm trying to import a .csv file. One column has text from a website comment filed, so there are all... Mirroring and performance - hi, anyone know how to maintain the performance of database while mirroring. Because we notice that the system become slower... SQL Server 2005 : SQL Server 2005 SecurityEvent ID 11 "There are multiple accounts with name MSSQLSvc/..." - I'm seeing this event in my domain system event logs: EventID : 11 There are multiple accounts with name MSSQLSvc/server1.domain.com:1433 of type DS_SERVICE_PRINCIPAL_NAME. EventID... SQL Server 2005 : SQL Server 2005 Performance TuningClient-Side Tracing: How? - Hi, Is there a way to do client-side tracing of the SQL commands being sent from a client box to a... Query Cost - Hi Team, As per my knowledge "Query Cost is a measure of the CPU and input-output resources used by the query" but... How to Use SQL Profiler - Hi, I want to trace all sql,stored proc with sql server profiler. So that I may able to extract top resource... SQL Server 2005 : SQL Server 2005 Integration ServicesAutomating Excel via SSIS and SQL Agent - Hi I have encountered an issue on a project that I'm rather struggling with and I'm hoping someone has encountered something... OLE DB Source -- SQL Command performance? - The following question is based on my zero-understanding of how SSIS performs regarding the use of server memory. Does someone know... windows file system operation - I am working on windows server 2003. I have a file 'target.file' at local. There is a 'target' folder on network... SQL Server 2005 : T-SQL (SS2K5)Delete column from multiple (hundreds) of tables - Hi, I have a rather large number of tables made using a (fairly) standard template. Due to a compatibility issue with... Slow Recursive CTE - Hi All I am using a CTE query for recording a list of purchase order numbers separated by a comma for... SQL Server 2005 : SQL Server NewbiesRestore Transaction Log Error: database not in NORECOVERY/STANDBY mode - Although I have been using SQL Server for many years, I am merely a database developer and extremely green at... SQL Server 7,2000 : AdministrationDTS to import Data from excel - Hi, I have one excel that has data like this: ADM14021360066,208930705 502151 Abraçadeiras Plasticas Wurth 280x4,8 UN 1000.1 NOVO 200 Camião ADM14021360066,208930706 502161 Abraçadeiras Plasticas Wurth 360x4,8 UN 1000.1 NOVO 300 Camião ADM14021360066,208930707 2 Barra Reparação Magnum UN 1000.1 NOVO 2 Camião ADM14021360066,208930708 3 Bicos Cola UN 1000.1 NOVO 70 Camião ADM14021360066,20 How to import Oracle dmp file into SQL Server 2000 or 2005? - Hi, Gurus Q for you.. I have a .dmp file that has been created by Oracle. I want to import this... SQL Server 7,2000 : Performance TuningHelp - Can't query a single table - Hi All, I have a table in SQL Server 2000 that cannot be queried. Even a simple "select count(*) from dbo_tblAllocations"... SQL Server 2008 : SQL Server 2008 - GeneralWhere to push-pin the object explorer? - It's a silly question but I couldn't find a answer, I don't know what quick key I touched but the... SSRS - changing the rendering of the print control? - I haven't started any research into this, just curious if anyone else has tried this. Is it possible to change... How to script profiler trace to save results to a table ? - Hi all, I created a trace in Profiler GUI. I set it to save results to a table and it is... Max meomory setting vs task manager - Sql production box has 108 GB RAM i allocated Max memory 100 GB for sql server ,in task manager i... Query Help - Hello Everyone I have one task I need to do pivoting [code="sql"] create table #Temp1 ( iSchoolYearCode int, dtCalendarDay datetime, iCalendarDatCategoryID int, vcCalendarCategoryCode varchar(25) ) insert into #Temp1 values (2012,'2012-08-30... Runnable query - waiting for what? - I have a query (basically a SELECT ... INTO tmp_table FROM .... (more than 100 joins in here). Its status is "runnable" - CPUTime... Finding min using over(order by) in sql server - Hi All, I am trying to write a sql to find the minimum of a dataset i am using sql server. select ST.BG_DTTM ,MIN(ST.BG_DTTM)... Permissions issue upon a restore - Every now and then when we restore production databases in development environment we get a few emails from developers complaining... table value functions - i am passing table from C# to sql. on sql this is my code : [code="sql"] CREATE TYPE [dbo].[CampaignIdList] AS TABLE( [BLMJ] NVARCHAR(50)... error in bulk copy - Hi I want to test some changes in the structure of a table and see the plan,I exported the data in... Error reading a csv (already open in Excel) using SSIS - hi, I'm loading data into a table from a csv file using a data flow task. If the file is already open... Date in BCP out file name - Hi, How can I insert current date in BCP out file name? I use this command, in SQL JOB step (type: Operating... Cant create persisted computed column on a table - Hi Everyone, I am trying to add a computed column as - alter table MyTable add ComputeCol1 AS (Calc(col1) persisted When I executed... Extended Events for counting execution of Stored Proc - With my limited knowledge of extended events, I wrote the following: [code="sql"]IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='ExecCount_SPName') DROP EVENT SESSION ExecCount_SPName... SSIS Transfer Objects Task Problem with Check Constraints - I'm trying to develop a simple SSIS (2008) package that will be used to copy all the objects from a... SSIS Text qualifier question - I'm trying to replace an existing function with an SSIS package. Rows are exported to a comma delimited flat file... CMS Query Error Suppression - I'm not sure if there is a specific ability in queries against server groups or if there is something more... datatype for such a long integer - Hi I got a dataset from somewhere which I have to store in sql server. It has one column which consists... error while installing sql server 2008 r2 express in a windows 8 pc - I was trying to install sql server 2008 R2 in windows 8 pc. First i installed sql server 2008 r2 by... Really Strange - Hello Everyone I hope that your day has been better than mine. I have been working most of the day with a... SQL Server 2008 cluster upgrade - Hi Team, We have SQL Server 2008 active-passive cluster. We got requirement to upgrade it SQL Server 2008 R2. Can you... Waring alters when the database (files MDF, NDF & LDF) reaching the MAX size. - HI Frnds, I need help on setting up alters when the database (files MDF, NDF & LDF) reaching the MAX size.... Display on Month and Year from date formula ?? - Hi, I want to display only MM/YY from the formula of @enddate -21?? Thanks How to rectify the mistake of specifying wrong path in Alter database <db name> modify file... command - I was trying to learn the movement of system databases from one drive to another. i satarted with msdb,model and... PREEMPTIVE_OS_FILEOPS - I have a job configured to run a DBCC CHECKDB on all of the databases on my server every 2... xp_cmdshell access denied to desktop file when logged as administrator - why ? - I am logged in as an administrator and each time i run the following query exec master.dbo.xp_cmdshell 'dir c:\users\administrator.mydomainname\desktop\resumes\*.*' I get... SQL Server 2008 : T-SQL (SS2K8)Find Row Number Based on Column Information - Hey Guys, I'm trying to find the ItemCode of the TreeType 'S' Above the Line that I'm running the query... Grouping sets - Hi All, how to use the groupingsets to a query having more than 32 columns plz help out Finding min using over(order by) in sql server - Hi All, I am trying to write a sql to find the minimum of a dataset select ST.BG_DTTM ,MIN(ST.BG_DTTM) over(ORDER BY ST.BG_DTTM) AS... How to compare parent child data in SQL - Hi; I have two SQL tables like below; [CODE]T1: relationID, meterID, parentID, childID T2: dataID, meterID, date, amount[/CODE] Sample data of tables; [CODE] T1 T2 -----------------... Loading T-SQL Statements from a database - I want to call all of my heading from table A and call my select statements from Table B. TABLE A ID... TSQL Help - Hi all, Need some help in writing a query for the below scenario. I have two table with start and end... Get distinct values into single column - Hi - I am having trouble writing a query to get the distinct values of a column grouping into a single... Issue with crypt_gen_random - This is my scalar function, which returns numbers between @min and @max (both included): [code] create function GetRandom(@min int, @max int) returns int as begin declare... SQL Server 2008 : SQL Server Newbiesit takes too long to insert data to different tables - I was trying to dump data from a Temp table to 4 different tables (Computers, ComputerInfo, Vulnerabilities, and AuditInfo) in... Clustered indexed primary key not in asc order when selected - Hi All, I have a table that has a primary key which is a clustered index. However when I select data based... Creating a Ref Number usning letters and numbers. - I have a column on my database called Booking Number this is to be used on correspondence etc however I... Creating a BookingDate using a random function - I have an arrival date already in the system and I need to populate the Booking date using a random... Delete taking time due to foreign keys - Dear All We have a base table and 10 other tables related with foreign keys to this. One of the foreign... Update query will not run? - The following query gives me a random date between 1 and 28 days after the arrival date: SELECT ArrivalDate, DATEADD(day, 1... Max Server Memory - We just took over a small network with 2k3 running Sql 2005. We virtualized it, per customer requirement, and seeing... table `forums` where register threads - table `forums` where register threads Hello everyone. Thanks all in advance for any help and suggestions. I've the table `forums` where register threads,... Count by 30 min interval query - I need help in querying a table for volume by 30 minute interval by day month by month. Table dbo.HisT contained... SQL Server 2008 : Security (SS2K8)Script Logins - Hi All Is there any way to script out all the Logins on a SQL Server instance, including server level privileges,... Login Auditing - I have been asked to provide documentation on where SQL Server displays messages for login auditing. We set the auditing... SQL Server 2008 : SQL Server 2008 AdministrationMSSQL Server is not Active - Hi All, I had a ticket with the following message: MSSQL Server is not Active Or ITM6 monitoring agent datacollection has been... Monitoring SQL database - I've got a database in production, which is growing on it's own about 1 GB an hour. The database is... Physical reads - I am a little confused about the concept of physical reads: In Extended event, I add an event, and filter by... DBCC consistency error - Hi Team Last week we run dbcc command on few databases and found the below consistency errors in the database ,no... Net Send alert - Dear Experts What is the net send alert error, how it works, Thanks lot log restore to new db name - I have restored a database to a new name. The original name is forms. The new database is forms_scott(Restoring). When... SQL Cluster Gateway Info Lost on Node Move or Failure Simulation - Hello All, First time poster so if this has been asked, please forgive. I have searched the forums but didn't find... VMWare Virtual SQL Server Administration - I need to find out what the concensus is on virtualizing SQL server. I know that VMWare reports the more processors... SSIS ForEach Loop Container - loop through all DB's - I am trying to get the FELC to loop through all the databases for my sql server. I want to... DBCC 802 error - Hello guys, one of my customer reported 802 error in his server. Could you help me how to track circumstances leading... Differential backups rendered useless by backup device - This is my situation. Hope someone can give some advice. I created a maintenance plan for a production database that takes... SQLServerCentral.com : Anything that is NOT about SQL!advice on how to promote my skillset? - Hi everybody, I didn't really know where to post this question, but I guess this sub-forum could do. I apologise in... 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 ServicesSSRS Parameters - Autocomplete - Hi geniuses! It is possible to have the autocomplete features in a SSRS report parameter? Thanks in advance! Best Regards! Export to PDF problem from Report manager - The report exports to pdf from Visual studio but when trying to export from Report manager it throws an error.... but can they replace SSRS? - Do you know of alternatives to row and column quantitative reporting besides Qlikview and Tableau? I think both are smashing... Hiding the Show/Hide details button on the Report Server - Although you can make a folder invisible in the view list, if you click on show details, the folder shows... Database Design : Design Ideas and QuestionsPolicy database Design - i have enclosed here sheet which contains column's list . can anybody help how can i seggregate them into different tables... Data Warehousing : Integration ServicesCan I use transactions for non database related control flow tasks - Guys, I have a package that has two tasks. The first is an SQL task which updates two tables (batch headers... Prevent flat file from being written (SSIS 2008r2) - SO In the project I'm working I'm importing csv files from a dynamic map to a database. As an extra feauture... Restricting Data before using OLE DB Destination - I just used the unpivot tool. So now I have more columns than I need. I know how to map... Looking for a way to move files from a dynamic source location to another dynamic archive location - I got the following situation: A database with a settings table and archive tables. (In the setting table we store the location... script task - Hi All, i have been using ssis for few years now i have used most of the components/task but never script task i... Data Warehousing : Analysis ServicesRelated measures - I have two related tables in my DSV. The first table called Test has a primary key column called test_id... |
|||||||||||||||||
|
|
|||||||||||||||||
|
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. All rights reserved. Contact: webmaster@sqlservercentral.com |
|||||||||||||||||
Filtered Indexes - Simple Partitioning without Enterprise Edition?
Resource Governor in SQL Server 2012
Running SQLIO with Multiple Files
I thought this was a great blog post and it got me to read, even though it's a topic I know about, have written about, and didn't think the post would reveal anything amazing: 
