Tag Info

New answers tagged

1

I would recommend to Update statistics as well as compatibility level to the latest version of sql server (provided your application supports it and you have done proper full business life cycle testing). I have written about full list of steps when moving from lower version to higher version As a side note, please don't use sp_MSforEachtable - Its not ...


0

[Note: I got to the end of writing this and noticed that sometimes you refer to the data field as image and sometimes as attach_data. You'll need to take that into account as you read this.] You might try breaking up the update statement into multiple batches with TOP: UPDATE TOP (5) messages SET attach_data = 0x WHERE ins_date < DATEADD(DAY, -120, ...


0

Consider indexing properly and running the cleanup once every minute. That way each batch is automatically tiny. Each day has 1440 such small batches then.


0

If you want to discover performance issues sooner than later, don't wait until the production phase. Perform automated performance testing earlier in the development lifecycle when it is less costly to make changes. Follow the same pattern as automated database unit testing, except with a larger database and a data-driven test that includes both expected ...


0

Ok this is what i've referenced thesqldude.com/tag/makecert for creating self signed certificate. JIST : 1) MAKECERT tool used for preparing certificate with command mentioned in link above – FYR : makecert -r -pe -n "CN=cs020.classic.classicinformatics.net" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp ...


5

Obviously the backup can't be completed if it was still in progress when electricity stopped flowing. That doesn't necessarily mean it is corrupt, but it is almost certainly going to be unusable. In the unlucky (though unlikely) event that you lose power during a full backup, and the database itself was also corrupted as a result (this is not necessarily ...


1

One more very simple solution: use notepad++. Inside there is very good plugin called Poor man's sql formatter. The drawback is that you have to paste SQL inside the notepad++. But it is free. There is also a web page, but I did not try it.


2

Just adding files to the existing file group will not redistribute the existing data. I would go the route @Max is suggesting and ensure that the new files you are creating are the same size to maximize the proportional fill feature. And before you begin take a full backup. Once you've migrated all of the tables, indexes, etc over to the new file group ...


3

Quick Answer What do you do if: you don't want to write a program (for example, PowerShell)? the Client Tools haven't been installed (required to use SQLCMD.EXE)? There is either no SQL Server log file, or there is no log file that contains any start/restart events? SQL Server is not running (hence SELECT @@VERSION; is not an option)? Just run the ...


5

Which option from above will perform better? Best case, both will produce exactly the same execution plan, with the same runtime performance. This can require some careful design and some fairly advanced skills, as Rob Farley mentions in his answer. Rob also has a blog post describing the core issue, and it is also discussed in one of his chapters from ...


-3

Just Execute the query select serverproperty('Edition') to find out your SQL Server Edition


1

Start by working out the duration per minute: select sum(duration) as dpm, datediff(minute, '2015-06-5 13:00', time) as mins_since_start from table where time >= '2015-06-5 13:00' and time < '2015-06-5 14:00' group by datediff(minute, '2015-06-5 13:00', time) Now get the top 5 percent of this. select top (5) percent sum(duration) as dpm, ...


2

I show some important things about views in my talk at http://bit.ly/Simplification - the key thing would be to make sure that you're not doing needless joins, that they get optimised out when you don't need those columns. My talk generally covers the idea of modularisation for an interface for developers, so it's probably quite useful.


1

For SQL Server, using a similar example with a clustered index on the Id, the plans both use a "Clustered Index Seek" which is a broad explanation for what may happen internally. select ObjectId from oav.Object where ObjectId in (3105680,3105681,3105682,3105683,3105684,3105685,3105686,3105687,3105688,3105689) select ObjectId from oav.Object where ...


0

I'm having some difficulty understanding your goals, but below is code based on my interpretation. This is in the context of your sql-server tag. This is completely self-contained, with sample data generation provided. CREATE TABLE DataTable ( Duration int DEFAULT ABS(CHECKSUM(NEWID())) % 180, [Time] datetime2(0) DEFAULT DATEADD(minute, ...


0

If I understand you're requirements; perhaps the following will help: USE tempdb; IF EXISTS (SELECT 1 FROM sys.objects o WHERE o.name = 't') DROP TABLE dbo.t; CREATE TABLE dbo.t ( Item VARCHAR(255) , duration INT , dtime DATETIME ); INSERT INTO dbo.t (Item, duration, dtime) VALUES ('one', 27, '2015-07-30 19:06:00') , ('one', 27, ...


5

SORT_IN_TEMPDB means that SQL server will use tempdb to allocate the temporary space as opposed to allocating space in the user database whose index is being rebuild. This means you will need less free space in your user database during an index rebuild operation and more free space in tempdb. It gives you better advantage when tempdb is on a different set ...


0

At some point, I am doing the query correctly I just neglected the round brackets in the pending and on-going where clause. My bad. My query is Update HotelTable set Status = 'Finished' where DetailsID in (Select DT.DetailsID from DetailsTable as [DT] where DT.Hotel_placeID in (Select DT.Hotel_placeID from HotelTable as [HT] inner join DetailsTable ...


2

Instead of using "Copy-and-paste" to copy from the user-interface; use a T-SQL script to copy the rows from one table to another. Setup a test-bed with two tables, one will be the source, the other the destination: USE tempdb; CREATE TABLE dbo.StatusTable1 ( StatusNumber NUMERIC(18,10) ); CREATE TABLE dbo.StatusTable2 ( StatusNumber NUMERIC(28,2) ...


0

You need to look at the actual catalog/metadata table(s) and clean them up. The GUI and stored procedures often don't cleanup the underlying tables properly. So, after you have run the sp_dropsubscription SP run a select from MSsubscriptions and MSsubscriptions to see if there is any data referring to your unwanted subscriptions. See: ...


1

Right off the bat, if you're targeting a column insert ( insert table ( col_1, col_2 ) ), you need to specify an insert value for both columns. From the posted syntax, it appears you're targeting 3 columns ( user_id, group_id, default_group ) but only inserting 1 explicit value ( user_group.user_id ). To start, I recommend commenting out the insert portion ...


2

Your INSERT statement is malformed; you may want to try something like: Insert into usergroup (user_id, group_id, default_group) Select ug.user_id, 1234 AS group_id, ug.default_group from [user_group] ug join [group] g on ug.group_id = g.id where g.name = 'someName'; Replace 1234 in the query above with the id of the group you wish to add for each user ...


2

Michael Green is right: the devs are trying to thwart parameter sniffing, which happens when SQL Server compiles a plan that is great for one set of parameter values, but horrible for others. You'll want to use OPTION (RECOMPILE) on the statement(s) with issues, not WITH RECOMPILE on the procedure. And I wouldn't recommend the local variables "trick" - it ...


4

First, let's clarify terms. AlwaysOn is a Microsoft marketing term which includes both SQL Server Availability Groups and SQL Server Failover Cluster Instances. You should stop using AlwaysOn and be specific for all future communications. If you are referring to FCIs: the configuration should be completely transparent to your application. They should ...


-2

In this scenario system table has always been useful. Please use this query. Select * From SYS.Comments Where Text = 'Stored Procedure Name' Although this table as multiple rows for a single object_id but putting a concat function on the table helps to get a single ROW for a single object_id.


4

The problem mentioned on MSDN has nothing to do with sys.sql_modules or OBJECT_DEFINITION(); they've misinterpreted the problem. What actually happened is they were thwarted by an output limitation in Management Studio, which by default will only show 255 characters and at most 8192 in any output tuple in Results to Text. So first, make sure you change this ...


0

count lines/records in your textfile import count records after import if count1=count2 then continue tidy up fields (update blablabla set changefield='new' where myfield='badinfo') test tidy up : select count(*) from blablabla where myfield='badinfo' and changefield<>'new' if testTidyUp-count = 0 then cleanup = correct ... continue count records after ...


2

I would use the Object Explorer in SQL Server Management Studio and go from the top, one procedure at a time. Right click on the procedure. Select Modify. Edit the procedure code. List item Press F5 to execute the modification of the procedure. Close the tab. Go to 1. Makes it easy to divide the work between you and your friend that does the same going ...


0

To get a notification when a job fails you can set the notifications from the job itself. Go to the job, then: Open job properties. Check for Notifications options. You will find options like Email, Page etc. Check the email option and set the operator to be emailed. Make sure you create the operator and set its email in the SQL Server Agent. ...


0

You can use DBMail whenever you need to. For example you can create SQL Server Job and send emails to persons which is responsible for the results from that job. Or you can receive alerts from SQL Server using DBMail. From my point of view in general: if you want to make some work inside SQL server and send mail for that you will use DBMail.


0

Creating a listener creates (permissions must allow, otherwise the entry will be made by the network team) a DNS entry, and has a dependency to an IP address. It logically follows that each listener needs its own IP address, because how else will traffic know where to be directed? See Failover Cluster Manager>[Cluster Name]>Services and applications> ...


2

The only actions that will cause the values in your configuration table to be updated within SSIS will be when you are in the Configuration editor menu. Clicking OK there will cause the values to be written to the table. Now, if you have an Execute SQL Task that has UPDATE statement in it, that would obviously change the values in the table but those would ...


1

The problem with a FromDate / ToDate format is that the query generates an index scan. For example, a staff member takes vacation and returns two weeks later. ID FromDate ToDate 15 2011-01-03 2011-01-17 Imagine many staff members, some with 20 or 30 years of such work history, maybe taking only a day or two at a time several times each year. ...


0

Thank you guys. It looks like the backup was corrupted or got damamged while moving from one server to the other and that's why I wasn't able to do BACK/RESTORE before. I copied backup and all .mdf, .ndf, .ldf files over to the new server. And ran following command: USE [master] GO RESTORE DATABASE VDA FROM DISK = 'D:\Program Files\Microsoft SQL ...


0

for a database that is NOT involved in replication I have added my notes below. -- script to restore database IntEng2 -- to a point in time 22-aug-2014 at 11:30 AM -- marcelo miorelli -- 22-aug-2014 --================================================ -- 6 THINGS TO DO BEFORE DOING A RESTORE: --================================================ ...


1

we have run into one point of contention between developers and dba's: they will not run traces against production database servers. It depends on what you asking your DBAs to trace (I am only talking about server side trace). We have a 24x7 trace running which captures minimal stuff which helps us in troubleshooting specific scenarios e.g. User error ...


4

The latest version of the RML Utilities (09.04.0051 as at today) support Extended Events tracing, so maybe you could work with your DBAs to do some controlled tracing. A screenshot from the help file: They do however have a point because the default trace template provided with RML includes statement-level events (SQL:StmtStarting, SQL:StmtCompleted, ...


2

Use the built-in bitwise operators to examine the column. For example select schema_option & 0x01 AS GeneratesObjectCreation, schema_option & 0x02 AS GenerateCustomSP, ... etc.


3

I believe its just a message and not an error unless and until the task is getting impacted when this message is logged in the error log: We also encountered the same for one of our production server, where windows admin team enabled the SQL Server backup through VSS (Volume Shadow Copy Services). And when this process runs it freezes I/O temporarily to ...


4

The main strengths of SSIS as I see it are 1) the ability to do things across servers which aren't otherwise linked (eg via linked servers) and 2) the ability to do things in parallel (eg multiple Execute SQL tasks operating concurrently). If you are using traditional SQL then you probably don't need 1), but you might need 2). Your ETL must be running in ...


3

1. Why Statistics IO show higher reads than Profiler? No idea, sorry. There are often differences because they are measuring different things as alluded to in the knowledge base article. There is no additional documentation on this that I am aware of. You might be able to infer some things through detailed testing, but there's really no guarantee whatever ...


0

You would need to specify if you are referring to Azure SQL Database (PaaS) or SQL Server on an Azure VM (IaaS). As already noted TDE with Azure SQL Database is only available with V12 which is only available in certain regions. If you an Azure VM is what you are working with you can implement TDE in SQL Server after the VM is built, even after SQL Server ...


3

SQL Azure Transparent Data Encryption is possible, but it is currently still in preview. If you do not want to accept the supplemental terms of use for this then you can simply install SQL Server on the VM you just created or provision a new machine and use one of the numerous pre-built images that are available. I would recommend you simply spin up a new ...


0

Most existing tools like Microsoft Sync and Merge-Replication ended seeming likebeing way too much overkill and being more hassle than they'd be worth. This is my SQL Script to attach the databases CREATE DATABASE LocalDatabase ON (Filename = 'C:\ProgramData\Clayton\Database.mdf') , (Filename = 'C:\ProgramData\Clayton\Database_log.ldf') FOR ATTACH; ...


0

Zerothly, you need to test your backups and have a recovery plan if your not doing that. Lets talk about some quick win performance first of all. Here are some options. Google can help flesh these out more. Are their multiple named instances on the box? Have you set max memory? Have you set the number of files for tempdb? If its at all possible, put the ...


4

In order to capture messages (either from PRINT or RAISERROR('', 1, 10) or like it) in .NET (regardless of calling a Stored Procedure or ad hoc SQL), you need to set up a method that will get called by the SqlConnection.InfoMessage event. The basic implementation is as follows: [SqlProcedure()] public static void Print_CLR() { using (SqlConnection conn ...


2

Double check that the paths you are trying to restore to exist on the D drive. SQL Server will not create the directory path if it doesn't exist. If you haven't already, run the restore command with the verify only option MSDN. If that is successful here are a few additional things you can try to isolate why it is failing If you have the space on the ...


0

Use this DOS command with OS greater than 2007: ->> WMIC CPU Get DeviceID,NumberOfCores,NumberOfLogicalProcessors For Physical servers DeviceID ~ Socket, NumberofCores ~ CPU's, NumberOfLogicalProcessors ~ Threads. MS now uses a core-based license model. CAL's are out of scope. Search "Microsoft SQL Server 2012 Licensing Guide" if further ...


1

I wasn't able to find any good resources online, so I did some more hands-on research and thought it would be useful to post the resulting full-text maintenance plan we are implementing based on that research. Our heuristic to determine when maintenance is needed Our primary goal is to retain consistent full-text query performance as data evolves in the ...


0

You have a table that contains All (Active and Inactive) Doctors and you have a file that contains Active Doctors. The question then becomes how can you determine who isn't in the file? The SSIS way to do this is to use the Lookup Task, just as you are already doing but instead, you're going to use a Cache Connection Manager to allow you to use the flat ...



Top 50 recent answers are included