Hi,
I recieved an alert that says 'The Auto Shrink flag for database is not set according to best practice.' Can anyone tell me where I can find this flag and what it shoul dbe set to?
Thanks
Af.
In order to use Microsoft Dynamics NAV with the SQL Server 2005, I need to set Trace Flag 4616, and I know it's a setting somwhere, where I can add this flag no. -but I can't remember where? Please help?
View 1 Replies View Related
I created a package wide OnError event handler with the following script:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'
Dts.Variables("FailureFlag").Value = True
'
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
I ran the package with an error and set a break on error condition. When I looked at the value of "FailureFlag" it was False. Any ideas why it is not being set?
I have a post execute event that is supposed to rollback from a snapshot on failure and it is not doing so on failure so I'm trying to figure out what's wrong.
what's this : "auto shrink " db option in properties ???
in which case may i use this option ?
please help
I am using MSDE in sql server 2000.
When the database data file size exceed 2G limit, I tried to shrink the db and it
reduced by about 100MB.
2 days later, I checked the db data file size and it was reduced to 550MB, which is
1/4 of the original db size!
Please kindly explain the reason of this case, and how can it be monitored
effectively?
Thank you.
I would like to do an update statement to set auto shrinks to zero. A value of one means they are on. I have been using this select statement and manually changing them, but can't use it in a where clause.
select name, databasepropertyex(name, 'isautoshrink') shrink
from master.dbo.sysdatabases
I would like to do "where shrink = 1", but it won't work.
Any suggestions?
Hi, I am trying to automate shrinking the transaction log file on SQL server.
Every so often we get errors with our application using SQL server, in which I resolve by running the backup log and shrink log commands. However, recently I got the error: Could not allocate space for object 'table_name' in database 'database_name' because the 'Primary' file group is full. To resolve this I had to create another transaction log and then run the backup log and shrink log commands.
I know need to automate the process of shrinking the log file. I have checked and the Auto Shrink checkbox is ticked but these errors still occur.
How can I delete the additional log file I created and automate this task of shrinking the log file within SQL server? Any help would be appreciated...Thanks
Is Auto shrink is good option where database is very critical and there is no down time?
View 4 Replies View RelatedIs it possible/advisable to change this setting with users connected? Â There are a number of web based users and an agent job running every 30 seconds.Â
USE [master]
GO
ALTER DATABASE [Bla] SET AUTO_SHRINK OFF WITH NO_WAIT
Hello everybody,First: SQL Server 2000 sp3a, HP cluster server, MS 2003 server,database recovery model simpleTorn page detection:When I have this option turned on, processes conected with updating andwriting in database, was MUCH more slower, than if I've this optionturned off.It seams to me, that when this option is turned on, the sqlserver ismuch more slower by any operation connected with writing, than withoutthis option.This option is required for ensurance, that the data are writtenproperly on the media.My question is:if I have my database running under cluster, with RAID hd system, whichcontains write cache, with additional checking, that the data will bewritten correctly, is it necessary for me to keep this option turnedon?Auto shrink:When it is turned on, does it affect performance?Personaly, I don't think so, but somebody is insysting on it, and Iwould like to have an information from independent source about that.Best regardsMatik
View 13 Replies View RelatedIs it possible to manually force/call/start the system AUTOSHRINK process? I have an issue that appears only when the engine shrinking process is running and I need this to reproduce my bug.
I know how to start a "regular" database shrink process with:DBCC SHRINKDATABASE(xxxx);, but this is not the same as one started from the database engine.
Here's my predicament. I changed the filter on an article within a subscription, but not in a meaningful way. I just added "database.dbo." before a column name, but now my subscription is flagged for reinitialization, and won't replicate any transactions unless I start over with a new snapshot. I don't want that to happen. How can I reset the article properties somewhere to get it back the way it was and continue replicating transactions.
(I needed to add "database.dbo" so that another job that looks at the filter info would point to the correct database. Next time maybe I'll just modify the table that stores the filter info)
The reason I don't want to snapshot from the beginning is that I have procedures on the subscribing server that fire triggers from the transactions, so all the jobs "downstream" would get all out of sync.
HELP !
Since upgrading from SQL Server Management Studio 2008 R2, I've noticed that it no longer autosaves queries that have not been manually saved first. If a file has been manually saved the autorecover files end up in the following directory:
%appdata%MicrosoftSQL Server Management Studio11.0AutoRecoverDatSolution1
However, I have ended up in the situation where I have unsaved queries when my computer has crashed and have not been able to recover them.
I have also found references to .sql files stored in temp files in the following directory, but the files here seem to be very haphazardly caught:
%userprofile%AppDataLocalTemp
If you have problems shrinking a transaction log no matter what commands you issue, here's a way to shrink the tranlog:
1. Right click on the properties of the database and go to the options tab.
2. Change the recovery mode to simple.
3. Right click again on the database - go to all tasks - shrink database.
4. Shrink the database.
5. Change the recovery mode back to what you had it as.
I found this out by trial and error as I could not find any documentation on it and no matter what I tried, I could not get the transaction log to shrink.
I have an MS SQL Server table with a Job Number field I need this field to start at a certain number then auto increment from there. Is there a way to do this programatically or within MSDE?
Thanks, Justin.
hi all!
i have columns.
1. Flag : stores only + / - / <NULL>
2. Number : stores float
how would i select this 2 columns to generate -ve and +ve number depend on the flag? eg :
Flag : -
Number : 4 ====> -4 and +4 if flag=+
when i try to just to combine with -(Flag) for -ve, it gave converting type error even i already CAST it..
tq in advance
~~~Focus on problem, not solution ¯(º_o)/¯ ~~~
Hi,
I'm trying to use trace flag 1204 to get some detailed deadlock information
In EM, I add startup parameter -T1204, and then I stop and start the server.
I run two jobs that I have setup to deadlock, and they do. But no information about the deadlock is in my sql error log? anyone know what I am doing wrong? thanks.
Guys,
I have following scenario where I need to populate version flag based on the partyid, count and seq columns.
Here is it how it should populate for each new count of a partyid the max(seq) should be versioned as 1
For example
PARTYCOUNT SEQ VERSION
_________________________________________________
10011 1
10111 0
10112 1
10121 0
10122 0
10123 1
10231 1
Is there any way to populate this version flag.
Any sugggestions and inputs would help
Thanks
I have a perl script (uses sql query) that will run if there is anyupdate in the row. Currently, I am a trigger that sets the DateModifiedfield with getdate() Then my perl script looks for certain range ofmodified dates and runs the necessary query.I am trying to come up with a different mechanism where I don't have touse the date field. I am looking into flag bits. But not sure howto use it.Should I create a trigger that will set the flag bit after any update?This way the script will just look for the updated records, regardlessof what time it was updated. Maybe I am misunderstanding flag bits, thenafter another subsequent update how would the perl/sql script know whichrecords were updated? I hope this make sense.thanks.--s
View 3 Replies View Related
Hi there,
I'm working on a program and I want it to check to see if the database it is pointed to has had a backup done against the database in the last 10 days. I will then throw a message box indicating that the database has not been backed up and that they should consider doing so.
Is there any attibute that I can check to see when the last time the database was backed up?
THanks for your input! - Eric -
I am trying to build a decision tree algorithm with multiple regressors. I am having trouble changing the properties for the input variables to regressors (the only multiple flag choice is "not null"). I created a mining model using the DMX query thinking I could change the code to include multiple regressors, however I get a syntax error when trying to execute the model. Also, I am wondering if you can view the dmx code for a model built using the mining wizard.
My query is as follows:
CREATE MINING MODEL BVRSlotq1052
(Machine Long Key,
[Banksize] LONG CONTINUOUS REGRESSOR ,
[Cabinet] Text Discrete,
[CUID] Long Continuous PREDICT_ONLY,
[Denom] Text Discrete,
[Gametype] Text Discrete,
[Max Coins] Text Discrete,
[Par] Long Continuous,
[Pos] Text Discrete,
[Progressive] Text Discrete,
[Type]Text Discrete)
Using Microsoft_Decision_Trees
If you have a table with columns like Category int, Cost money, EffectiveDate DateTime, SpecialFlag (char Y or N) how would you write a query to get for each category, get the most recent, with a specialFlag of Y if a Y record exists and if not, grab the N record?
I have tried using the exists clause for the flag, but as soon as I mix in the group by to get the latest effectivedate, the max is evaluated first and I am not guaranteed to use the Y records if they exist.
To illustrate:
A 1, $10, 1/1/2007 Y
B 1, $12, 1/1/2007 N
C 2, $5, 6/7/2007 N
D 2, $4, 1/1/2007 N
E 3, $1, 1/1/2007 Y
F 3, $2, 6/7/2007 Y
G 3, $3, 2/1/2008 N
I want records A because it is flagged Y, C because it is the most recent and there is no Y record and F because it is flagged and most recent.
I am using this select statement to radomly display a recordSelectCommand="SELECT TOP 1 * FROM [TBL_Example] ORDER BY NEWID()I need to, however, flag this record, to determine if it has already been previously randomly selected, and won't take part in future random selections.I will need to add a where clause to the above, but what I am unsure of is what I should do for the insert statement. I guess I could figure this out on my own as well if I could determine a means to prgramatically store my PK from the above record in session.Any ideas?
View 1 Replies View RelatedI have a table where when an employee works LEAVE1 and LEAVE2 paycodes on the same day then in the new column called 'FLAG' we need to put a 1 for LEAVE1 and a 2 for LEAVE2, when these paycodes are not there on the same day then flag will be NULL as for other paycodes.
Tried using the CASE statements but it always puts a 1 or 2 for the respective paycodes regardless of the fact whether they were together on the same day or not.
WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS, PAYCODE) AS
(
SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3', 'REG1' UNION ALL
SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '09:00','13:00','4','REG2' UNION ALL
SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '09:00','13:00','4','LEAVE1' UNION ALL
SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '14:00','16:00','2', 'LEAVE2'UNION ALL
SELECT 1234,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'LEAVE1'UNION ALL
SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'REG1'UNION ALL
SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '10:00','12:00','2','LEAVE2'
)
[code].....
Current Results
PersonTransactDate STARTDATEEND_DATEIN_PUNCH OUT_PUNCH HOURS PAYCODE
123408/03/2015 08/03/201508/03/201506:00 13:00 3 REG1
123408/03/2015 08/03/201508/03/201506:00 13:00 4 REG2
123408/04/2015 08/04/201508/04/201509:00 16:00 4 LEAVE1
123408/04/2015 08/04/201508/04/201509:00 16:00 2 LEAVE2
123408/05/2015 08/05/201508/05/201508:00 09:00 1 LEAVE1
455308/05/2015 08/05/201508/05/201508:00 12:00 2 LEAVE2
455308/05/2015 08/05/201508/05/201508:00 12:00 1 REG1
Desired Results
PersonTransactDate STARTDATEEND_DATEIN_PUNCH OUT_PUNCH HOURS PAYCODE FLAG
123408/03/2015 08/03/201508/03/201506:00 13:00 3 REG1 NULL
123408/03/2015 08/03/201508/03/201506:00 13:00 4 REG2 NULL
123408/04/2015 08/04/201508/04/201509:00 16:00 4 LEAVE1 1
123408/04/2015 08/04/201508/04/201509:00 16:00 2 LEAVE2 2
123408/05/2015 08/05/201508/05/201508:00 09:00 1 LEAVE1 NULL
455308/05/2015 08/05/201508/05/201508:00 12:00 2 LEAVE2 NULL
455308/05/2015 08/05/201508/05/201508:00 12:00 1 REG1 NULL
i want to make a stored procedure that will update the notification flag in the table.
If the email was succesfully sent the flag will be updated as SENT. And if it is un successful the flag will be NOT SENT in column of the table in SQL. The email will be send using .NET codes and updates of notification flag will use SQL stored procedure. Please help
Table Example:
ID NAME FLAG
1 Bill null
2 Bill null
3 Bill null
4 Bob null
5 Henry null
6 Henry null
I want to keep the flag empty for one record and mark the rest as duplicates so the output would be:
ID NAME FLAG
1 Bill null
2 Bill D
3 Bill D
4 Bob null
5 Henry null
6 Henry D
How can I go about doing this?
i have an hours and a seperate minutes column
e.g.
Hour
10
Minute
15
I now need to flag the last two hours of the day in a new column both datatypes are INT
I have a table with 4 columns,let say ID, WorkId , HireDate ,TerminateDate
My source date
ID  WorkId  HireDate   TerminateDate
1Â Â Â Â 123Â Â Â 2006-01-05Â Â 2013-01-13
1Â Â Â Â 456Â Â Â 2006-02-23Â Â 2013-04-13
1Â Â Â Â 789Â Â Â 2005-12-12Â Â 2013-06-13
2Â Â Â Â 913Â Â Â 2004-12-15Â Â 2012-01-13
2Â Â Â Â 406Â Â Â 2006-04-13Â Â 2014-04-13
3Â Â Â Â 113Â Â Â 2009-11-15Â Â 2011-01-13
3Â Â Â Â 106Â Â Â 2010-04-13Â Â
4Â Â Â Â 513Â Â Â 2002-11-15Â Â
4Â Â Â Â 168Â Â Â 2011-10-23Â Â
5Â Â Â Â 342Â Â Â 2007-01-05Â Â 2009-01-13Â Â Â Â Â Â
5Â Â Â Â 416Â Â Â 2008-02-23Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
5Â Â Â Â 159Â Â Â 2008-12-12Â Â 2010-06-13Â Â Â
6Â Â Â Â 542Â Â Â 2005-01-03Â Â 2007-01-13Â Â Â Â Â Â
Rule -1:Â set flag = yes
-Whenever terminatedate is not null
-Also with in the ID group if one record terminatedate is null then set flag=yes
Rule-2: Set flag=No
- with in the ID group Whenever terminatedate is null
My output should look like this..
ID  WorkId  HireDate   TerminateDate    Flag
1Â Â Â Â 123Â Â Â 2006-01-05Â Â 2013-01-13Â Â Â Â Â Â Â yes ( Rule-1: set flag = yes within the group for ID =1 because all the 3 records terminatedate is not null)
1Â Â Â Â 456Â Â Â 2006-02-23Â Â 2013-04-13Â Â Â Â Â Â Â yes
1Â Â Â Â 789Â Â Â 2005-12-12Â Â 2013-06-13Â Â Â Â Â Â Â yes
 2    913   2004-12-15  2012-01-13       yes (Rule-1: set flag = yes within the group for ID =2 because all the 2 records terminatedate is not null)
[Code] .....
Hi,
Do i need to restart SQL Database Engine Service once I set trace flag (-T1222).
Regards
Sufian
is there a way to automate a job to wait on a flag in a directory and to start my maintenance plans ?
if so how would i code is there external functions plz help.
Knifflige Frage zu Datenbanken:Es gibt Tabellen, die mehr als ein PK-Feld haben; also z.B.MAID und GMID.Soweit ok.Nehmen wir an, aus dieser Tabelle sollen keine Datensätze physikalischgelöscht werden, sondern der Löschstatus durch ein Datenfeld GELOESCHTangegeben werden. Hat das Feld den Wert 0, dann ist der Datensatz nichtgelöscht; ansonsten steht der Zeitpunkt (Tag und Uhrzeit) der Löschung drin.Dann funktioniert der PK nicht mehr, denn es können ja durchaus mehrereDatensätze mit gleicher MAID und GMID vorhanden sein. Aufnehmen vonGELOESCHT in den PK geht theoretisch, ist aber gefährlich (daFließkommafeld, und wer weiß schon, ob 0 immer gleich 0 ist...).Hat jemand eine Idee?SWE@KP
View 11 Replies View Related
Hi all
i am using Sql Server 2005 SP2, I am trying to configure Mirroring. The issue is ,After configuring mirroring ,Whem i am starting START MIRRORING button .I am getting the error
Database mirroring is disabled by default. Database mirroring is currently provided for evaluation purposes only and is not to be used in production environments. To enable database mirroring for evaluation purposes, use trace flag 1400 during startup. For more information about trace flags and startup options, see SQL Server Books Online. (Microsoft SQL Server, Error: 1498)
I used BDCC TRACEON (1400) and I altered the Startup parameter in configuration manager of SQL SERVER ;-T1400
But still I am getting the above error which is indicated in red
Please help me to fix the issue
I found trace flag 9520 is in the startup parameters, but I cannot find what the trace flag does from internet.
View 6 Replies View Related