ben alter - Page 68 - Developer IT

toSource in internet explorer

- by Ben Shelock

Is there an alternative method toSource for internet explorer?

read the dictionary value from array

CALORIES = \ { 'Beef' : 200, \ 'Chicken' : 140, \ } class Food(): __slots__ = ( 'cal' # Calories ) def mkFood( name ): """Create and return a newly initialized Food item""" result = Food() result.cal = calorie in dict(CALORIES[1]) return result Is that a proper way to the value of the target item in Calories? Like getting 200, 140, such like that. result.cal = calorie in dict(CALORIES[1])

Read the article

Opposite of .find()

- by Ben

Is there an opposite of .find()? Where $('.myclass').except('#myid'); Would grab all elements with .myclass except the element with #myid. I know I could do $('.myclass[id=myid]') in this example, but seems like it would be helpful in other cases. Thanks

Read the article

Can I get.seed() somehow?

- by a different ben

In reference to the statement set.seed(), can I get the seed instead after running some code if I didn't set it explicitly? I've been re-running some code containing a function that randomises some sample of the input data (the function is part of the kohonen package). After playing with it for some time to see the variety of output (it was an 'unstable' problem), I noticed one result that was pretty interesting. I of course had not used set.seed(), but wondered if I could get the seed after running the code to reproduce the result? In ?set.seed I see .Random.seed saves the seed set for the uniform random-number generator But I don't know how that helps.

Read the article

Find the contents of a directory

- by Ben Shelock

Is it possible to get the contents of a directory with javascript?

Read the article

SQL: How to use a column that was just added

- by sbenderli

I am trying to add 2 columns and then I would like to set some values to them but I get a compile-error saying the column does not exist. I am using the following script: IF NOT EXISTS (select column_name from INFORMATION_SCHEMA.columns where table_name = 'SYSTM_FRM' and column_name = 'SF_Ip_TXT') ALTER TABLE SYSTM_FRM add SF_Ip_TXT NVARCHAR(20) IF NOT EXISTS (select column_name from INFORMATION_SCHEMA.columns where table_name = 'SYSTM_FRM' and column_name = 'SF_Port_NUM') ALTER TABLE SYSTM_FRM add SF_Port_NUM int Update dbo.SYSTM_FRM SET dbo.SYSTM_FRM.SF_Ip_TXT = dbo.FRM.FRM_Ip_TXT, dbo.SYSTM_FRM.SF_Port_NUM = dbo.FRM.FRM_Port_NUM FROM dbo.FRM INNER JOIN dbo.SYSTM_FRM ON dbo.FRM.FRM_RCRD_NUM = dbo.SYSTM_FRM.SF_FrameRecord_NUM Is there any way to use a column that I am adding in the same script?

Read the article

[Bash] Save part of matching pattern to variable

- by Ben

I want to extract a substring matching a pattern and save it to a file. An example string: Apr 12 19:24:17 PC_NMG kernel: sd 11:0:0:0: [sdf] Attached SCSI removable disk I want to extract the part between the brackets, in this case []. I tried to do something like grep -e '[$subtext]' to save the text in the brackets to a variable. Of course it doesn't work, but I am looking for a way similar to this. It would be very elegant to include a variable in a regex like this. What can I do best? Thanks!

Read the article

PHP+MySQL Update TimeStamp and get NOW() back

- by Ben

Is it possible to merge these two mysql queries into one? I want to get NOW() returned to a php variable. mysql_query('INSERT INTO translate (IDRef, RefType, Lang, Text, LastChangeTS) VALUES ('.$id.', \''.$reftype.'\', \''.$lang.'\', \''.$text.'\', NOW()) ON DUPLICATE KEY UPDATE text = \''.$text.'\', LastChangeTS = NOW()'); mysql_query('SELECT LastChangeTS FROM translate WHERE IDRef = '.$id.' AND RefType = \''.$reftype.'\' AND Lang = \''.$lang.'\'');

Read the article

Make content 100% height with scrollbar

- by Ben Sinclair

I am using CKEditor (http://ckeditor.com) and I've created my own custom file browser... Problem is, when you open the filebrowser, it opens it in a new popup window and has no scrollbars. I submitted a support ticket 2 weeks ago to find out how to add the scrollbars and no answer. I can't find what to edit in the code either... So what I plan to do is make it scroll using CSS... I found this post about making the body 100% (http://stackoverflow.com/questions/886809/css-quirks-html-4-01-strict-100-body-height-and-scrollbars) but it doesn't add scrollbars. Any ideas how I can force scrollbars using CSS?

Read the article

Centre content in a <div>

- by Ben

Hi, this is probably very simple to do, but i cant quite get it right. I have a which contains two tags. I would like the tags to be both centred vertically and horizontally. I have set them to centre horizontally by setting the text-align:center; on the div, but cant figure out how to vertically centre them. How would i go about doing this? Thanks.

Read the article

Strange issue with cout

- by ben

After reading from a text file, and storing it into a string, I tried to pass this message into another function that will print it out. This is what it looks like: http://imgur.com/MCjfRdp void insert (int id, string message){ cout << "Inserting " << message << " at" << endl; } Somehow the message behind the string overrides the message. But once I removed the " at" after message, it worked as expected. http://imgur.com/JdHPPmi void insert (int id, string message){ cout << "Inserting " << message << endl; } I somehow suspect the problem came from stringstream, but i couldn't find out where. Here's the code that read from file vector <string> line; fstream infile; string readLine, tempLine, action, tempLine1; int level, no; infile.open(fileName.c_str(),ios::in); int i = 0; while (!infile.eof()) { getline (infile, readLine); line.push_back(readLine); } infile.close(); line.pop_back(); for (int i = 0; i < line.size();i++) { stringstream ss (line.at(i)); getline (ss, action, ' '); if (action == "init") { // some other work here } else if (action == "insert") { tempLine = line.at(i); ss >> no; stringstream iline(tempLine); getline (iline, tempLine1, ' '); getline (iline, tempLine1, ' '); getline (iline, tempLine1, '\n'); Insert (no, tempLine1); } Since my file has different kinds of actions, here is what test.dat contains: insert 0 THIS IS A TEST edit: When i did file.txt, it came out as something like this. Inserting THIS IS A TEST at Inserting THIS IS TEST NO 2 at

Read the article

How can I remove Ruby, Rails & mysql to start the installation process again?

- by ben

I've been trying to get setup with Ruby on Rails today, but I think I've followed some bad instructions along the way, and nothing seems to work. I've now borrowed the book "Agile Web Development with Rails, Third Edition" from a friend, and want to follow the setup instructions in that. Firstly, do I need to remove what I've setup previously? If yes, how do I do it? I can't seem to find instructions anywhere. I'm running OSX 10.6.1, so I know that it came with some stuff already setup, but I've been installing customized stuff over the top which I think I'll have to remove. Thanks for reading!

Read the article

Wrapping <%= f.check_box %> inside <label>

- by Ben Scheirman

I have a list of checkboxes on a form. Due to the way the CSS is structured, the label element is styled directly. This requires me to nest the checkbox inside of the tag. This works in raw HTML, if you click on the label text, the state of the checkbox changes. It doesn't work with the rails <%= f.check_box %> helper, however, because it outputs a hidden input tag first. In summary, <label> <%= f.check_box :foo %> Foo </label> this is the output I want: <label> <input type="checkbox" ... /> <input type="hidden" ... /> Foo </label> ...but this is what rails is giving me: <label> <input type="hidden" ... /> <input type="checkbox" ... /> Foo </label> So the label behavior doesn't actually work :(. Is there any way to get around this?

Read the article

Cannot log into Oracle Enterprise Manager 11g: ORA-28001

- by Álvaro G. Vicario

I can no longer log into Oracle Enterprise Manager 11g. I get this error message: ORA-28001: the password has expired (DBD ERROR: OCISessionBegin) I could log into the server using SQL*Plus. I warned me that the password was going to expire in 7 days (which is not the same as being already expired). Following advice from several documents, I ran these commands from SQL*Plus: ALTER USER sys IDENTIFIED BY new_password; ALTER USER system IDENTIFIED BY new_password; SQL*Plus no longer warns about passwords, but I still cannot use the Enterprise Manager. Then I followed this to remove password expiration: ALTER PROFILE default LIMIT password_life_time UNLIMITED And I've also restarted the Oracle services. In case it was using cached credentials, I've tried to connect from several browsers in several computers. No way: I still get ORA-28001 in Enterprise Manager. What am I missing? Update: Some more info SQL> select username,ACCOUNT_STATUS,EXPIRY_DATE from dba_users; USERNAME ACCOUNT_STATUS EXPIRY_D ------------------------------ -------------------------------- -------- MGMT_VIEW OPEN SYS OPEN SYSTEM OPEN [...] DBSNMP EXPIRED 24/05/10 SYSMAN EXPIRED 24/05/10 OUTLN EXPIRED & LOCKED 16/11/09 FLOWS_FILES EXPIRED & LOCKED 16/11/09 USERNAME ACCOUNT_STATUS EXPIRY_D ------------------------------ -------------------------------- -------- MDSYS EXPIRED & LOCKED 16/11/09 ORDSYS EXPIRED & LOCKED 16/11/09 EXFSYS EXPIRED & LOCKED 16/11/09 WMSYS EXPIRED & LOCKED 16/11/09 WKSYS EXPIRED & LOCKED 16/11/09 WK_TEST EXPIRED & LOCKED 16/11/09 CTXSYS EXPIRED & LOCKED 16/11/09 ANONYMOUS EXPIRED & LOCKED 16/11/09 XDB EXPIRED & LOCKED 16/11/09 WKPROXY EXPIRED & LOCKED 16/11/09 ORDPLUGINS EXPIRED & LOCKED 16/11/09 USERNAME ACCOUNT_STATUS EXPIRY_D ------------------------------ -------------------------------- -------- FLOWS_030000 EXPIRED & LOCKED 16/11/09 OWBSYS EXPIRED & LOCKED 16/11/09 SI_INFORMTN_SCHEMA EXPIRED & LOCKED 16/11/09 OLAPSYS EXPIRED & LOCKED 16/11/09 SCOTT EXPIRED & LOCKED 16/11/09 ORACLE_OCM EXPIRED & LOCKED 16/11/09 TSMSYS EXPIRED & LOCKED 16/11/09 XS$NULL EXPIRED & LOCKED 16/11/09 BI EXPIRED & LOCKED 16/11/09 PM EXPIRED & LOCKED 16/11/09 MDDATA EXPIRED & LOCKED 16/11/09 USERNAME ACCOUNT_STATUS EXPIRY_D ------------------------------ -------------------------------- -------- IX EXPIRED & LOCKED 16/11/09 SH EXPIRED & LOCKED 16/11/09 DIP EXPIRED & LOCKED OE EXPIRED & LOCKED 16/11/09 APEX_PUBLIC_USER EXPIRED & LOCKED 16/11/09 HR EXPIRED & LOCKED 16/11/09 SPATIAL_CSW_ADMIN_USR EXPIRED & LOCKED 16/11/09 SPATIAL_WFS_ADMIN_USR EXPIRED & LOCKED 16/11/09

Read the article

An XEvent a Day (3 of 31) – Managing Event Sessions

- by Jonathan Kehayias

Yesterdays post, Querying the Extended Events Metadata , showed how to discover the objects available for use in Extended Events. In todays post, we’ll take a look at the DDL Commands that are used to create and manage Event Sessions based on the objects available in the system. Like other objects inside of SQL Server, there are three DDL commands that are used with Extended Events; CREATE EVENT SESSION , ALTER EVENT SESSION , and DROP EVENT SESSION . The command names are self...(read more)

Read the article

Visual Studio 2010 and SQLCLR: Some Good, Some Bad

- by Adam Machanic

This past week I've been trying out Visual Studio 2010 for SQLCLR development. Verdict: A couple of nice things, a couple not so nice. In the interest of keeping things somewhat positive around here, we'll start with the good stuff : Pre-deployment and post-deployment scripts are built in. This is great, especially if you're working with features such as ordered TVFs, which Visual Studio 2008 never properly supported. In 2010 you can stick the ALTER FUNCTION in a post-deployment script and you'll...(read more)

Read the article

SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance

- by pinaldave

Earlier, I had written two articles related to Shrinking Database. I wrote about why Shrinking Database is not good. SQL SERVER – SHRINKDATABASE For Every Database in the SQL Server SQL SERVER – What the Business Says Is Not What the Business Wants I received many comments on Why Database Shrinking is bad. Today we will go over a very interesting example that I have created for the same. Here are the quick steps of the example. Create a test database Create two tables and populate with data Check the size of both the tables Size of database is very low Check the Fragmentation of one table Fragmentation will be very low Truncate another table Check the size of the table Check the fragmentation of the one table Fragmentation will be very low SHRINK Database Check the size of the table Check the fragmentation of the one table Fragmentation will be very HIGH REBUILD index on one table Check the size of the table Size of database is very HIGH Check the fragmentation of the one table Fragmentation will be very low Here is the script for the same. USE MASTER GO CREATE DATABASE ShrinkIsBed GO USE ShrinkIsBed GO -- Name of the Database and Size SELECT name, (size*8) Size_KB FROM sys.database_files GO -- Create FirstTable CREATE TABLE FirstTable (ID INT, FirstName VARCHAR(100), LastName VARCHAR(100), City VARCHAR(100)) GO -- Create Clustered Index on ID CREATE CLUSTERED INDEX [IX_FirstTable_ID] ON FirstTable ( [ID] ASC ) ON [PRIMARY] GO -- Create SecondTable CREATE TABLE SecondTable (ID INT, FirstName VARCHAR(100), LastName VARCHAR(100), City VARCHAR(100)) GO -- Create Clustered Index on ID CREATE CLUSTERED INDEX [IX_SecondTable_ID] ON SecondTable ( [ID] ASC ) ON [PRIMARY] GO -- Insert One Hundred Thousand Records INSERT INTO FirstTable (ID,FirstName,LastName,City) SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID, 'Bob', CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith' ELSE 'Brown' END, CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York' WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino' WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles' ELSE 'Houston' END FROM sys.all_objects a CROSS JOIN sys.all_objects b GO -- Name of the Database and Size SELECT name, (size*8) Size_KB FROM sys.database_files GO -- Insert One Hundred Thousand Records INSERT INTO SecondTable (ID,FirstName,LastName,City) SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID, 'Bob', CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith' ELSE 'Brown' END, CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York' WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino' WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles' ELSE 'Houston' END FROM sys.all_objects a CROSS JOIN sys.all_objects b GO -- Name of the Database and Size SELECT name, (size*8) Size_KB FROM sys.database_files GO -- Check Fragmentations in the database SELECT avg_fragmentation_in_percent, fragment_count FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED') GO Let us check the table size and fragmentation. Now let us TRUNCATE the table and check the size and Fragmentation. USE MASTER GO CREATE DATABASE ShrinkIsBed GO USE ShrinkIsBed GO -- Name of the Database and Size SELECT name, (size*8) Size_KB FROM sys.database_files GO -- Create FirstTable CREATE TABLE FirstTable (ID INT, FirstName VARCHAR(100), LastName VARCHAR(100), City VARCHAR(100)) GO -- Create Clustered Index on ID CREATE CLUSTERED INDEX [IX_FirstTable_ID] ON FirstTable ( [ID] ASC ) ON [PRIMARY] GO -- Create SecondTable CREATE TABLE SecondTable (ID INT, FirstName VARCHAR(100), LastName VARCHAR(100), City VARCHAR(100)) GO -- Create Clustered Index on ID CREATE CLUSTERED INDEX [IX_SecondTable_ID] ON SecondTable ( [ID] ASC ) ON [PRIMARY] GO -- Insert One Hundred Thousand Records INSERT INTO FirstTable (ID,FirstName,LastName,City) SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID, 'Bob', CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith' ELSE 'Brown' END, CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York' WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino' WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles' ELSE 'Houston' END FROM sys.all_objects a CROSS JOIN sys.all_objects b GO -- Name of the Database and Size SELECT name, (size*8) Size_KB FROM sys.database_files GO -- Insert One Hundred Thousand Records INSERT INTO SecondTable (ID,FirstName,LastName,City) SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID, 'Bob', CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith' ELSE 'Brown' END, CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York' WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino' WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles' ELSE 'Houston' END FROM sys.all_objects a CROSS JOIN sys.all_objects b GO -- Name of the Database and Size SELECT name, (size*8) Size_KB FROM sys.database_files GO -- Check Fragmentations in the database SELECT avg_fragmentation_in_percent, fragment_count FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED') GO You can clearly see that after TRUNCATE, the size of the database is not reduced and it is still the same as before TRUNCATE operation. After the Shrinking database operation, we were able to reduce the size of the database. If you notice the fragmentation, it is considerably high. The major problem with the Shrink operation is that it increases fragmentation of the database to very high value. Higher fragmentation reduces the performance of the database as reading from that particular table becomes very expensive. One of the ways to reduce the fragmentation is to rebuild index on the database. Let us rebuild the index and observe fragmentation and database size. -- Rebuild Index on FirstTable ALTER INDEX IX_SecondTable_ID ON SecondTable REBUILD GO -- Name of the Database and Size SELECT name, (size*8) Size_KB FROM sys.database_files GO -- Check Fragmentations in the database SELECT avg_fragmentation_in_percent, fragment_count FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED') GO You can notice that after rebuilding, Fragmentation reduces to a very low value (almost same to original value); however the database size increases way higher than the original. Before rebuilding, the size of the database was 5 MB, and after rebuilding, it is around 20 MB. Regular rebuilding the index is rebuild in the same user database where the index is placed. This usually increases the size of the database. Look at irony of the Shrinking database. One person shrinks the database to gain space (thinking it will help performance), which leads to increase in fragmentation (reducing performance). To reduce the fragmentation, one rebuilds index, which leads to size of the database to increase way more than the original size of the database (before shrinking). Well, by Shrinking, one did not gain what he was looking for usually. Rebuild indexing is not the best suggestion as that will create database grow again. I have always remembered the excellent post from Paul Randal regarding Shrinking the database is bad. I suggest every one to read that for accuracy and interesting conversation. Let us run following script where we Shrink the database and REORGANIZE. -- Name of the Database and Size SELECT name, (size*8) Size_KB FROM sys.database_files GO -- Check Fragmentations in the database SELECT avg_fragmentation_in_percent, fragment_count FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED') GO -- Shrink the Database DBCC SHRINKDATABASE (ShrinkIsBed); GO -- Name of the Database and Size SELECT name, (size*8) Size_KB FROM sys.database_files GO -- Check Fragmentations in the database SELECT avg_fragmentation_in_percent, fragment_count FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED') GO -- Rebuild Index on FirstTable ALTER INDEX IX_SecondTable_ID ON SecondTable REORGANIZE GO -- Name of the Database and Size SELECT name, (size*8) Size_KB FROM sys.database_files GO -- Check Fragmentations in the database SELECT avg_fragmentation_in_percent, fragment_count FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED') GO You can see that REORGANIZE does not increase the size of the database or remove the fragmentation. Again, I no way suggest that REORGANIZE is the solution over here. This is purely observation using demo. Read the blog post of Paul Randal. Following script will clean up the database -- Clean up USE MASTER GO ALTER DATABASE ShrinkIsBed SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO DROP DATABASE ShrinkIsBed GO There are few valid cases of the Shrinking database as well, but that is not covered in this blog post. We will cover that area some other time in future. Additionally, one can rebuild index in the tempdb as well, and we will also talk about the same in future. Brent has written a good summary blog post as well. Are you Shrinking your database? Well, when are you going to stop Shrinking it? Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Index, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology

Read the article

AutoAudit 1.10c

- by Paul Nielsen

AutoAudit is a free SQL Server (2005, 2008) Code-Gen utility that creates Audit Trail Triggers with: · Created, Modified, and RowVersion (incrementing INT) columns to table · Creates View to reconstruct deleted rows · Creates UDF to reconstruct Row History · Schema Audit Trigger to track schema changes · Re-code-gens triggers when Alter Table changes the table Version 1.10c Adds: · Createdby and ModifiedBy columns. Pass the user to the column and AutoAudit records that username instead of the Suser_Sname...(read more)

Read the article

Fragmented Log files could be slowing down your database

- by Fatherjack

Something that is sometimes forgotten by a lot of DBAs is the fact that database log files get fragmented in the same way that you get fragmentation in a data file. The cause is very different but the effect is the same – too much effort reading and writing data. Data files get fragmented as data is changed through normal system activity, INSERTs, UPDATEs and DELETEs cause fragmentation and most experienced DBAs are monitoring their indexes for fragmentation and dealing with it accordingly. However, you don’t hear about so many working on their log files. How can a log file get fragmented? I’m glad you asked. When you create a database there are at least two files created on the disk storage; an mdf for the data and an ldf for the log file (you can also have ndf files for extra data storage but that’s off topic for now). It is wholly possible to have more than one log file but in most cases there is little point in creating more than one as the log file is written to in a ‘wrap-around’ method (more on that later). When a log file is created at the time that a database is created the file is actually sub divided into a number of virtual log files (VLFs). The number and size of these VLFs depends on the size chosen for the log file. VLFs are also created in the space added to a log file when a log file growth event takes place. Do you have your log files set to auto grow? Then you have potentially been introducing many VLFs into your log file. Let’s get to see how many VLFs we have in a brand new database. USE master GO CREATE DATABASE VLF_Test ON ( NAME = VLF_Test, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.ROCK_2008\MSSQL\DATA\VLF_Test.mdf', SIZE = 100, MAXSIZE = 500, FILEGROWTH = 50 ) LOG ON ( NAME = VLF_Test_Log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.ROCK_2008\MSSQL\DATA\VLF_Test_log.ldf', SIZE = 5MB, MAXSIZE = 250MB, FILEGROWTH = 5MB ); go USE VLF_Test go DBCC LOGINFO; The results of this are firstly a new database is created with specified files sizes and the the DBCC LOGINFO results are returned to the script editor. The DBCC LOGINFO results have plenty of interesting information in them but lets first note there are 4 rows of information, this relates to the fact that 4 VLFs have been created in the log file. The values in the FileSize column are the sizes of each VLF in bytes, you will see that the last one to be created is slightly larger than the others. So, a 5MB log file has 4 VLFs of roughly 1.25 MB. Lets alter the CREATE DATABASE script to create a log file that’s a bit bigger and see what happens. Alter the code above so that the log file details are replaced by LOG ON ( NAME = VLF_Test_Log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.ROCK_2008\MSSQL\DATA\VLF_Test_log.ldf', SIZE = 1GB, MAXSIZE = 25GB, FILEGROWTH = 1GB ); With a bigger log file specified we get more VLFs What if we make it bigger again? LOG ON ( NAME = VLF_Test_Log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.ROCK_2008\MSSQL\DATA\VLF_Test_log.ldf', SIZE = 5GB, MAXSIZE = 250GB, FILEGROWTH = 5GB ); This time we see more VLFs are created within our log file. We now have our 5GB log file comprised of 16 files of 320MB each. In fact these sizes fall into all the ranges that control the VLF creation criteria – what a coincidence! The rules that are followed when a log file is created or has it’s size increased are pretty basic. If the file growth is lower than 64MB then 4 VLFs are created If the growth is between 64MB and 1GB then 8 VLFs are created If the growth is greater than 1GB then 16 VLFs are created. Now the potential for chaos comes if the default values and settings for log file growth are used. By default a database log file gets a 1MB log file with unlimited growth in steps of 10%. The database we just created is 6 MB, let’s add some data and see what happens. USE vlf_test go -- we need somewhere to put the data so, a table is in order IF OBJECT_ID('A_Table') IS NOT NULL DROP TABLE A_Table go CREATE TABLE A_Table ( Col_A int IDENTITY, Col_B CHAR(8000) ) GO -- Let's check the state of the log file -- 4 VLFs found EXECUTE ('DBCC LOGINFO'); go -- We can go ahead and insert some data and then check the state of the log file again INSERT A_Table (col_b) SELECT TOP 500 REPLICATE('a',2000) FROM sys.columns AS sc, sys.columns AS sc2 GO -- insert 500 rows and we get 22 VLFs EXECUTE ('DBCC LOGINFO'); go -- Let's insert more rows INSERT A_Table (col_b) SELECT TOP 2000 REPLICATE('a',2000) FROM sys.columns AS sc, sys.columns AS sc2 GO 10 -- insert 2000 rows, in 10 batches and we suddenly have 107 VLFs EXECUTE ('DBCC LOGINFO'); Well, that escalated quickly! Our log file is split, internally, into 107 fragments after a few thousand inserts. The same happens with any logged transactions, I just chose to illustrate this with INSERTs. Having too many VLFs can cause performance degradation at times of database start up, log backup and log restore operations so it’s well worth keeping a check on this property. How do we prevent excessive VLF creation? Creating the database with larger files and also with larger growth steps and actively choosing to grow your databases rather than leaving it to the Auto Grow event can make sure that the growths are made with a size that is optimal. How do we resolve a situation of a database with too many VLFs? This process needs to be done when the database is under little or no stress so that you don’t affect system users. The steps are: BACKUP LOG YourDBName TO YourBackupDestinationOfChoice Shrink the log file to its smallest possible size DBCC SHRINKFILE(FileNameOfTLogHere, TRUNCATEONLY) * Re-size the log file to the size you want it to, taking in to account your expected needs for the coming months or year. ALTER DATABASE YourDBName MODIFY FILE ( NAME = FileNameOfTLogHere, SIZE = TheSizeYouWantItToBeIn_MB) * – If you don’t know the file name of your log file then run sp_helpfile while you are connected to the database that you want to work on and you will get the details you need. The resize step can take quite a while This is already detailed far better than I can explain it by Kimberley Tripp in her blog 8-Steps-to-better-Transaction-Log-throughput.aspx. The result of this will be a log file with a VLF count according to the bullet list above. Knowing when VLFs are being created By complete coincidence while I have been writing this blog (it’s been quite some time from it’s inception to going live) Jonathan Kehayias from SQLSkills.com has written a great article on how to track database file growth using Event Notifications and Service Broker. I strongly recommend taking a look at it as this is going to catch any sneaky auto grows that take place and let you know about them right away. Hassle free monitoring of VLFs If you are lucky or wise enough to be using SQL Monitor or another monitoring tool that let’s you write your own custom metrics then you can keep an eye on this very easily. There is a custom metric for VLFs (written by Stuart Ainsworth) already on the site and there are some others there are very useful so take a moment or two to look around while you are there. Resources MSDN – http://msdn.microsoft.com/en-us/library/ms179355(v=sql.105).aspx Kimberly Tripp from SQLSkills.com – http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx Thomas LaRock at Simple-Talk.com – http://www.simple-talk.com/sql/database-administration/monitoring-sql-server-virtual-log-file-fragmentation/ Disclosure I am a Friend of Red Gate. This means that I am more than likely to say good things about Red Gate DBA and Developer tools. No matter how awesome I make them sound, take the time to compare them with other products before you contact the Red Gate sales team to make your order.

Read the article

SQL SERVER – 2011 – Introduction to SEQUENCE – Simple Example of SEQUENCE

- by pinaldave

SQL Server 2011 will contain one of the very interesting feature called SEQUENCE. I have waited for this feature for really long time. I am glad it is here finally. SEQUENCE allows you to define a single point of repository where SQL Server will maintain in memory counter. USE AdventureWorks2008R2 GO CREATE SEQUENCE [Seq] AS [int] START WITH 1 INCREMENT BY 1 MAXVALUE 20000 GO SEQUENCE is very interesting concept and I will write few blog post on this subject in future. Today we will see only working example of the same. Let us create a sequence. We can specify various values like start value, increment value as well maxvalue. -- First Run SELECT NEXT VALUE FOR Seq, c.CustomerID FROM Sales.Customer c GO -- Second Run SELECT NEXT VALUE FOR Seq, c.AccountNumber FROM Sales.Customer c GO Once the sequence is defined, it can be fetched using following method. Every single time new incremental value is provided, irrespective of sessions. Sequence will generate values till the max value specified. Once the max value is reached, query will stop and will return error message. Msg 11728, Level 16, State 1, Line 2 The sequence object ‘Seq’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated. We can restart the sequence from any particular value and it will work fine. -- Restart the Sequence ALTER SEQUENCE [Seq] RESTART WITH 1 GO -- Sequence Restarted SELECT NEXT VALUE FOR Seq, c.CustomerID FROM Sales.Customer c GO Let us do final clean up. -- Clean Up DROP SEQUENCE [Seq] GO There are lots of things one can find useful about this feature. We will see that in future posts. Here is the complete code for easy reference. USE AdventureWorks2008R2 GO CREATE SEQUENCE [Seq] AS [int] START WITH 1 INCREMENT BY 1 MAXVALUE 20000 GO -- First Run SELECT NEXT VALUE FOR Seq, c.CustomerID FROM Sales.Customer c GO -- Second Run SELECT NEXT VALUE FOR Seq, c.AccountNumber FROM Sales.Customer c GO -- Restart the Sequence ALTER SEQUENCE [Seq] RESTART WITH 1 GO -- Sequence Restarted SELECT NEXT VALUE FOR Seq, c.CustomerID FROM Sales.Customer c GO -- Clean Up DROP SEQUENCE [Seq] GO Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology

Read the article

Automatic Standby Recreation for Data Guard

- by pablo.boixeda(at)oracle.com

Hi,Unfortunately sometimes a Standby Instance needs to be recreated. This can happen for many reasons such as lost archive logs, standby data files, failover, among others.This is why we wanted to have one script to recreate standby instances in an easy way.This script recreates the standby considering some prereqs:-Database Version should be at least 11gR1-Dummy instance started on the standby node (Seeking to improve this so it won't be needed)-Broker configuration hasn't been removed-In our case we have two TNSNAMES files, one for the Standby creation (using SID) and the other one for production using service names (including broker service name)-Some environment variables set up by the environment db script (like ORACLE_HOME, PATH...)-The directory tree should not have been modified in the stanby hostWe are currently using it on our 11gR2 Data Guard tests.Any improvements will be welcome! Normal 0 21 false false false ES X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin-top:0cm; mso-para-margin-right:0cm; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0cm; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} #!/bin/ksh ### NOMBRE / VERSION ### recrea_dg.sh v.1.00 ### ### DESCRIPCION ### reacreacion de la Standby ### ### DEVUELVE ### 0 Creacion de STANDBY correcta ### 1 Fallo ### ### NOTAS ### Este shell script NO DEBE MODIFICARSE. ### Todas las variables y constantes necesarias se toman del entorno. ### ### MODIFICADO POR: FECHA: COMENTARIOS: ### --------------- ---------- ------------------------------------- ### Oracle 15/02/2011 Creacion. ### ### ### Cargar entorno ### V_ADMIN_DIR=`dirname $0` . ${V_ADMIN_DIR}/entorno_bd.sh 1>>/dev/null if [ $? -ne 0 ] then echo "Error Loading the environment." exit 1 fi V_RET=0 V_DATE=`/bin/date` V_DATE_F=`/bin/date +%Y%m%d_%H%M%S` V_LOGFILE=${V_TRAZAS}/recrea_dg_${V_DATE_F}.log exec 4>&1 tee ${V_FICH_LOG} >&4 |& exec 1>&p 2>&1 ### ### Variables para Recrear el Data Guard ### V_DB_BR=`echo ${V_DB_NAME}|tr '[:lower:]' '[:upper:]'` if [ "${ORACLE_SID}" = "${V_DB_NAME}01" ] then V_LOCAL_BR=${V_DB_BR}'01' V_REMOTE_BR=${V_DB_BR}'02' else V_LOCAL_BR=${V_DB_BR}'02' V_REMOTE_BR=${V_DB_BR}'01' fi echo " Getting local instance ROLE ${ORACLE_SID} ..." sqlplus -s /nolog 1>>/dev/null 2>&1 <<-! whenever sqlerror exit 1 connect / as sysdba variable salida number declare v_database_role v\$database.database_role%type; begin select database_role into v_database_role from v\$database; :salida := case v_database_role when 'PRIMARY' then 2 when 'PHYSICAL STANDBY' then 3 else 4 end; end; / exit :salida ! case $? in 1) echo " ERROR: Cannot get instance ROLE ." | tee -a ${V_LOGFILE} 2>&1 V_RET=1 ;; 2) echo " Local Instance with PRIMARY role." | tee -a ${V_LOGFILE} 2>&1 V_DB_ROLE_LCL=PRIMARY ;; 3) echo " Local Instance with PHYSICAL STANDBY role." | tee -a ${V_LOGFILE} 2>&1 V_DB_ROLE_LCL=STANDBY ;; *) echo " ERROR: UNKNOWN ROLE." | tee -a ${V_LOGFILE} 2>&1 V_RET=1 ;; esac if [ "${V_DB_ROLE_LCL}" = "PRIMARY" ] then echo "####################################################################" | tee -a ${V_LOGFILE} 2>&1 echo "${V_DATE} - Reacreating STANDBY Instance." | tee -a ${V_LOGFILE} 2>&1 echo "" | tee -a ${V_LOGFILE} 2>&1 echo "DATAFILES, CONTROL FILES, REDO LOGS and ARCHIVE LOGS in standby instance ${V_REMOTE_BR} will be removed" | tee -a ${V_LOGFILE} 2>&1 echo "" | tee -a ${V_LOGFILE} 2>&1 V_PRIMARY=${V_LOCAL_BR} V_STANDBY=${V_REMOTE_BR} fi if [ "${V_DB_ROLE_LCL}" = "STANDBY" ] then echo "####################################################################" | tee -a ${V_LOGFILE} 2>&1 echo "${V_DATE} - Reacreating STANDBY Instance." | tee -a ${V_LOGFILE} 2>&1 echo "" | tee -a ${V_LOGFILE} 2>&1 echo "DATAFILES, CONTROL FILES, REDO LOGS and ARCHIVE LOGS in standby instance ${V_LOCAL_BR} will be removed" | tee -a ${V_LOGFILE} 2>&1 echo "" | tee -a ${V_LOGFILE} 2>&1 V_PRIMARY=${V_REMOTE_BR} V_STANDBY=${V_LOCAL_BR} fi # Cargamos las variables de los hosts # Cargamos las variables de los hosts PRY_HOST=`sqlplus /nolog << EOF | grep KEEP | sed 's/KEEP//;s/[ ]//g' connect sys/${V_DB_PWD}@${V_PRIMARY} as sysdba select 'KEEP',host_name from v\\$instance; EOF` SBY_HOST=`sqlplus /nolog << EOF | grep KEEP | sed 's/KEEP//;s/[ ]//g' connect sys/${V_DB_PWD}@${V_STANDBY} as sysdba select 'KEEP',host_name from v\\$instance; EOF` echo "el HOST primary es: ${PRY_HOST}" | tee -a ${V_LOGFILE} 2>&1 echo "el HOST standby es: ${SBY_HOST}" | tee -a ${V_LOGFILE} 2>&1 echo "" | tee -a ${V_LOGFILE} 2>&1 ## ## Paramos la instancia STANDBY ## V_DATE=`/bin/date` echo "${V_DATE} - Shutting down Standby instance" | tee -a ${V_LOGFILE} 2>&1 echo "" | tee -a ${V_LOGFILE} 2>&1 echo "********************************************************************************" | tee -a ${V_LOGFILE} 2>&1 ## ## Paramos la instancia STANDBY ## SBY_STATUS=`sqlplus /nolog << EOF | grep KEEP | sed 's/KEEP//;s/[ ]//g' connect sys/${V_DB_PWD}@${V_STANDBY} as sysdba select 'KEEP',status from v\\$instance; EOF` if [ ${SBY_STATUS} = 'STARTED' ] || [ ${SBY_STATUS} = 'MOUNTED' ] || [ ${SBY_STATUS} = 'OPEN' ] then echo "${V_DATE} - Standby instance shutdown in progress..." | tee -a ${V_LOGFILE} 2>&1 echo "" | tee -a ${V_LOGFILE} 2>&1 echo "********************************************************************************" | tee -a ${V_LOGFILE} 2>&1 sqlplus -s /nolog 1>>/dev/null 2>&1 <<-! whenever sqlerror exit 1 connect sys/${V_DB_PWD}@${V_STANDBY} as sysdba shutdown abort ! fi V_DATE=`/bin/date` echo "" echo "${V_DATE} - Standby instance stopped" | tee -a ${V_LOGFILE} 2>&1 echo "" | tee -a ${V_LOGFILE} 2>&1 echo "********************************************************************************" | tee -a ${V_LOGFILE} 2>&1 ## ## Eliminamos los ficheros de la base de datos ## V_SBY_SID=`echo ${V_STANDBY}|tr '[:upper:]' '[:lower:]'` V_PRY_SID=`echo ${V_PRIMARY}|tr '[:upper:]' '[:lower:]'` ssh ${SBY_HOST} rm /opt/oracle/db/db${V_DB_NAME}/${V_SBY_SID}/data/*.dbf ssh ${SBY_HOST} rm /opt/oracle/db/db${V_DB_NAME}/${V_SBY_SID}/arch/*.arc ssh ${SBY_HOST} rm /opt/oracle/db/db${V_DB_NAME}/${V_SBY_SID}/ctl/*.ctl ssh ${SBY_HOST} rm /opt/oracle/db/db${V_DB_NAME}/${V_SBY_SID}/redo/*.ctl ssh ${SBY_HOST} rm /opt/oracle/db/db${V_DB_NAME}/${V_SBY_SID}/redo/*.rdo ## ## Startup nomount stby instance ## V_DATE=`/bin/date` echo "" | tee -a ${V_LOGFILE} 2>&1 echo "${V_DATE} - Starting DUMMY Standby Instance " | tee -a ${V_LOGFILE} 2>&1 echo "" | tee -a ${V_LOGFILE} 2>&1 echo "********************************************************************************" | tee -a ${V_LOGFILE} 2>&1 ssh ${SBY_HOST} touch /home/oracle/init_dg.ora ssh ${SBY_HOST} 'echo "DB_NAME='${V_DB_NAME}'">>/home/oracle/init_dg.ora' ssh ${SBY_HOST} touch /home/oracle/start_dummy.sh ssh ${SBY_HOST} 'echo "ORACLE_HOME=/opt/oracle/db/db'${V_DB_NAME}'/soft/db11.2.0.2 ">>/home/oracle/start_dummy.sh' ssh ${SBY_HOST} 'echo "export ORACLE_HOME">>/home/oracle/start_dummy.sh' ssh ${SBY_HOST} 'echo "PATH=\$ORACLE_HOME/bin:\$PATH">>/home/oracle/start_dummy.sh' ssh ${SBY_HOST} 'echo "export PATH">>/home/oracle/start_dummy.sh' ssh ${SBY_HOST} 'echo "ORACLE_SID='${V_SBY_SID}'">>/home/oracle/start_dummy.sh' ssh ${SBY_HOST} 'echo "export ORACLE_SID">>/home/oracle/start_dummy.sh' ssh ${SBY_HOST} 'echo "sqlplus -s /nolog <<-!" >>/home/oracle/start_dummy.sh' ssh ${SBY_HOST} 'echo " whenever sqlerror exit 1 ">>/home/oracle/start_dummy.sh' ssh ${SBY_HOST} 'echo " connect / as sysdba ">>/home/oracle/start_dummy.sh' ssh ${SBY_HOST} 'echo " startup nomount pfile='\''/home/oracle/init_dg.ora'\''">>/home/oracle/start_dummy.sh' ssh ${SBY_HOST} 'echo "! ">>/home/oracle/start_dummy.sh' ssh ${SBY_HOST} 'chmod 744 /home/oracle/start_dummy.sh' ssh ${SBY_HOST} 'sh /home/oracle/start_dummy.sh' ssh ${SBY_HOST} 'rm /home/oracle/start_dummy.sh' ssh ${SBY_HOST} 'rm /home/oracle/init_dg.ora' ## ## TNSNAMES change, specific for RMAN duplicate ## V_DATE=`/bin/date` echo "" | tee -a ${V_LOGFILE} 2>&1 echo "${V_DATE} - Setting up TNSNAMES in PRIMARY host " | tee -a ${V_LOGFILE} 2>&1 echo "" | tee -a ${V_LOGFILE} 2>&1 echo "********************************************************************************" | tee -a ${V_LOGFILE} 2>&1 ssh ${PRY_HOST} 'cp /opt/oracle/db/db'${V_DB_NAME}'/soft/db11.2.0.2/network/admin/tnsnames.ora.inst /opt/oracle/db/db'${V_DB_NAME}'/soft/db11.2.0.2/network/admin/tnsnames.ora' V_DATE=`/bin/date` echo "" | tee -a ${V_LOGFILE} 2>&1 echo "${V_DATE} - Starting STANDBY creation with RMAN.. " | tee -a ${V_LOGFILE} 2>&1 echo "" | tee -a ${V_LOGFILE} 2>&1 echo "********************************************************************************" | tee -a ${V_LOGFILE} 2>&1 rman<<-! >>${V_LOGFILE} connect target sys/${V_DB_PWD}@${V_PRIMARY} connect auxiliary sys/${V_DB_PWD}@${V_STANDBY} run { allocate channel prmy1 type disk; allocate channel prmy2 type disk; allocate channel prmy3 type disk; allocate channel prmy4 type disk; allocate auxiliary channel stby type disk; duplicate target database for standby from active database dorecover spfile parameter_value_convert '${V_PRY_SID}','${V_SBY_SID}' set control_files='/opt/oracle/db/db${V_DB_NAME}/${V_SBY_SID}/ctl/control01.ctl','/opt/oracle/db/db${V_DB_NAME}/${V_SBY_SID}/redo/control02.ctl' set db_file_name_convert='/opt/oracle/db/db${V_DB_NAME}/${V_PRY_SID}/','/opt/oracle/db/db${V_DB_NAME}/${V_SBY_SID}/' set log_file_name_convert='/opt/oracle/db/db${V_DB_NAME}/${V_PRY_SID}/','/opt/oracle/db/db${V_DB_NAME}/${V_SBY_SID}/' set 'db_unique_name'='${V_SBY_SID}' set log_archive_config='DG_CONFIG=(${V_PRIMARY},${V_STANDBY})' set fal_client='${V_STANDBY}' set fal_server='${V_PRIMARY}' set log_archive_dest_1='LOCATION=/opt/oracle/db/db${V_DB_NAME}/${V_SBY_SID}/arch DB_UNIQUE_NAME=${V_SBY_SID} MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' set log_archive_dest_2='SERVICE="${V_PRIMARY}"','SYNC AFFIRM DB_UNIQUE_NAME=${V_PRY_SID} DELAY=0 MAX_FAILURE=0 REOPEN=300 REGISTER VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)' nofilenamecheck ; } ! V_DATE=`/bin/date` if [ $? -ne 0 ] then echo "" echo "${V_DATE} - Error creating STANDBY instance" echo "" echo "********************************************************************************" else echo "" echo "${V_DATE} - STANDBY instance created SUCCESSFULLY " echo "" echo "********************************************************************************" fi sqlplus -s /nolog 1>>/dev/null 2>&1 <<-! whenever sqlerror exit 1 connect sys/${V_DB_PWD}@${V_STANDBY} as sysdba alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=${SBY_HOST})(PORT=1544))' scope=both; alter system set service_names='${V_DB_NAME}.eu.roca.net,${V_SBY_SID}.eu.roca.net,${V_SBY_SID}_DGMGRL.eu.roca.net' scope=both; alter database recover managed standby database using current logfile disconnect from session; alter system set dg_broker_start=true scope=both; ! ## ## TNSNAMES change, back to Production Mode ## V_DATE=`/bin/date` echo " " | tee -a ${V_LOGFILE} 2>&1 echo "${V_DATE} - Restoring TNSNAMES in PRIMARY " | tee -a ${V_LOGFILE} 2>&1 echo "" | tee -a ${V_LOGFILE} 2>&1 echo "********************************************************************************" | tee -a ${V_LOGFILE} 2>&1 ssh ${PRY_HOST} 'cp /opt/oracle/db/db'${V_DB_NAME}'/soft/db11.2.0.2/network/admin/tnsnames.ora.prod /opt/oracle/db/db'${V_DB_NAME}'/soft/db11.2.0.2/network/admin/tnsnames.ora' echo "" | tee -a ${V_LOGFILE} 2>&1 echo "${V_DATE} - Waiting for media recovery before check the DATA GUARD Broker" | tee -a ${V_LOGFILE} 2>&1 echo "" | tee -a ${V_LOGFILE} 2>&1 echo "********************************************************************************" | tee -a ${V_LOGFILE} 2>&1 sleep 200 dgmgrl <<-! | grep SUCCESS 1>/dev/null 2>&1 connect ${V_DB_USR}/${V_DB_PWD}@${V_STANDBY} show configuration verbose; ! if [ $? -ne 0 ] ; then echo " ERROR: El status del Broker no es SUCCESS" | tee -a ${V_LOGFILE} 2>&1 ; V_RET=1 else echo " DATA GUARD OK " | tee -a ${V_LOGFILE} 2>&1 ; Normal 0 21 false false false ES X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin-top:0cm; mso-para-margin-right:0cm; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0cm; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} V_RET=0 fi Hope it helps.

Read the article

Learn Who Started that Trace with the Default Trace

- by Jonathan Kehayias

This is not Extended Event related but it came from a question on Twitter about how to tell who and from what machine a server side trace was created, and there is no way to explain this in 140 characters so here’s a blog post. This information is tracked in the Default Trace and can be found by querying for EventClass 175 which is the Audit Server Alter Trace Event trace_event_id from sys.trace_events. select trace_event_id , name from sys . trace_events where name like '%trace%' To query...(read more)

Read the article

SQL SERVER – Difference Between CURRENT_TIMESTAMP and GETDATE() – CURRENT_TIMESTAMP Equivalent in SQL Server

- by pinaldave

A common question – I often get from Oracle/MySQL Professionals: “What is the Equivalent to CURRENT_TIMESTAMP in SQL Server?” Here is a common question I often get from SQL Server Professionals: “What are differences between Difference Between CURRENT_TIMESTAMP and GETDATE ()?” Very simple question but have showed up so frequently that I feel like to write about it. Well in SQL Server GETDATE() is Equivalent to CURRENT_TIMESTAMP. However, if you use CURRENT_TIMESTAMP in your select statement it will work fine. You can see in the above example – both of them returns the same value. Now let us go to next question regarding difference between GETDATE and CURRENT_TIMESTAMP. Well, the matter of the fact, there is no difference between them in SQL Server (Reference Link). CURRENT_TIMESTAMP is an ANSI SQL function, whereas GETDATE is T-SQL implementation of the same function. Both of them derive value from the operating system of the computer on which SQL Server instance is running. Above discussion prompts another question – in this case, what should one use GETDATE or CURRENT_TIMESTAMP? Well, this is indeed tricky and interesting question. I think I am very comfortable using the GETDATE () so I will go to use it but a matter of the fact there is no right or wrong answer. If you want to follow ancient saying “When in Rome, do as the Romans do”, I suggest using the GETDATE (), or continue using CURRENT_TIMESTAMP. With that said, there is one very important property we all need to keep in mind. If you use CURRENT_TIMESTAMP while creating an object, they are automatically converted to GETDATE() and stored internally. To illustrate what I am suggesting here is the example - Create a table using the following script CREATE TABLE [dbo].[TestTable]( [Cold2] [datetime] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[TestTable] ADD DEFAULT (CURRENT_TIMESTAMP) FOR [Cold2] GO Now go to SSMS and generate the script for the table and you will notice following syntax. CREATE TABLE [dbo].[TestTable]( [Cold2] [datetime] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[TestTable] ADD DEFAULT (GETDATE()) FOR [Cold2] GO You can notice that SQL Server have automatically converted CURRENT_TIMESTAMP to GETDATE(). I guess this gives us an idea how they behave. Now go ahead and make your choice! Do let me know which one will you use CURRENT_TIMESTAMP or GETDATE () in the comments area. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: PostADay, SQL, SQL Authority, SQL DateTime, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

Read the article

SQL Server 2008 Compression

- by Peter Larsson

Hi! Today I am going to talk about compression in SQL Server 2008. The data warehouse I currently design and develop holds historical data back to 1973. The data warehouse will have an other blog post laster due to it's complexity. However, the server has 60GB of memory (of which 48 is dedicated to SQL Server service), so all data didn't fit in memory and the SAN is not the fastest one around. So I decided to give compression a go, since we use Enterprise Edition anyway. This is the code I use to compress all tables with PAGE compression. DECLARE @SQL VARCHAR(MAX) DECLARE curTables CURSOR FOR SELECT 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(OBJECT_NAME(object_id)) + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)' FROM sys.tables OPEN curTables FETCH NEXT FROM curTables INTO @SQL WHILE @@FETCH_STATUS = 0 BEGIN IF @SQL IS NOT NULL RAISERROR(@SQL, 10, 1) WITH NOWAIT FETCH NEXT FROM curTables INTO @SQL END CLOSE curTables DEALLOCATE curTables Copy and paste the result to a new code window and execute the statements. One thing I noticed when doing this, is that the database grows with the same size as the table. If the database cannot grow this size, the operation fails. For me, I first ended up with orphaned connection. Not good. And this is the code I use to create the index compression statements DECLARE @SQL VARCHAR(MAX) DECLARE curIndexes CURSOR FOR SELECT 'ALTER INDEX ' + QUOTENAME(name) + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(OBJECT_NAME(object_id)) + ' REBUILD PARTITION = ALL WITH (FILLFACTOR = 100, DATA_COMPRESSION = PAGE)' FROM sys.indexes WHERE OBJECTPROPERTY(object_id, 'IsMSShipped') = 0 AND OBJECTPROPERTY(object_id, 'IsTable') = 1 ORDER BY CASE type_desc WHEN 'CLUSTERED' THEN 1 ELSE 2 END OPEN curIndexes FETCH NEXT FROM curIndexes INTO @SQL WHILE @@FETCH_STATUS = 0 BEGIN IF @SQL IS NOT NULL RAISERROR(@SQL, 10, 1) WITH NOWAIT FETCH NEXT FROM curIndexes INTO @SQL END CLOSE curIndexes DEALLOCATE curIndexes When this was done, I noticed that the 90GB database now only was 17GB. And most important, complete database now could reside in memory! After this I took care of the administrative tasks, backups. Here I copied the code from Management Studio because I didn't want to give too much time for this. The code looks like (notice the compression option). BACKUP DATABASE [Yoda] TO DISK = N'D:\Fileshare\Backup\Yoda.bak' WITH NOFORMAT, INIT, NAME = N'Yoda - Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM GO DECLARE @BackupSetID INT SELECT @BackupSetID = Position FROM msdb..backupset WHERE database_name = N'Yoda' AND backup_set_id =(SELECT MAX(backup_set_id) FROM msdb..backupset WHERE database_name = N'Yoda') IF @BackupSetID IS NULL RAISERROR(N'Verify failed. Backup information for database ''Yoda'' not found.', 16, 1) RESTORE VERIFYONLY FROM DISK = N'D:\Fileshare\Backup\Yoda.bak' WITH FILE = @BackupSetID, NOUNLOAD, NOREWIND GO After running the backup, the file size was even more reduced due to the zip-like compression algorithm used in SQL Server 2008. The file size? Only 9 GB. //Peso

Read the article

SQL SERVER – How to Force New Cardinality Estimation or Old Cardinality Estimation

- by Pinal Dave

After reading my initial two blog posts on New Cardinality Estimation, I received quite a few questions. Once I receive this question, I felt I should have clarified it earlier few things when I started to write about cardinality. Before continuing this blog, if you have not read it before I suggest you read following two blog posts. SQL SERVER – Simple Demo of New Cardinality Estimation Features of SQL Server 2014 SQL SERVER – Cardinality Estimation and Performance – SQL in Sixty Seconds #072 Q: Does new cardinality will improve performance of all of my queries? A: Remember, there is no 0 or 1 logic when it is about estimation. The general assumption is that most of the queries will be benefited by new cardinality estimation introduced in SQL Server 2014. That is why the generic advice is to set the compatibility level of the database to 120, which is for SQL Server 2014. Q: Is it possible that after changing cardinality estimation to new logic by setting the value to compatibility level to 120, I get degraded performance for few queries? A: Yes, it is possible. However, the number of the queries where this impact should be very less. Q: Can I still run my database in older compatibility level and force few queries to newer cardinality estimation logic? If yes, How? A: Yes, you can do that. You will need to force your query with trace flag 2312 to use newer cardinality estimation logic. USE AdventureWorks2014 GO -- Old Cardinality Estimation ALTER DATABASE AdventureWorks2014 SET COMPATIBILITY_LEVEL = 110 GO -- Using New Cardinality Estimation SELECT [AddressID],[AddressLine1],[City] FROM [Person].[Address] OPTION(QUERYTRACEON 2312);; -- Using Old Cardinality Estimation SELECT [AddressID],[AddressLine1],[City] FROM [Person].[Address]; GO Q: Can I run my database in newer compatibility level and force few queries to older cardinality estimation logic? If yes, How? A: Yes, you can do that. You will need to force your query with trace flag 9481 to use newer cardinality estimation logic. USE AdventureWorks2014 GO -- NEW Cardinality Estimation ALTER DATABASE AdventureWorks2014 SET COMPATIBILITY_LEVEL = 120 GO -- Using New Cardinality Estimation SELECT [AddressID],[AddressLine1],[City] FROM [Person].[Address]; -- Using Old Cardinality Estimation SELECT [AddressID],[AddressLine1],[City] FROM [Person].[Address] OPTION(QUERYTRACEON 9481); GO I guess, I have covered most of the questions so far I have received. If I have missed any questions, please send me again and I will include the same. Reference: Pinal Dave (http://blog.sqlauthority.com)Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL

Search Results

Search found 3097 results on 124 pages for 'ben alter'.

Page 68/124 | < Previous Page | 64 65 66 67 68 69 70 71 72 73 74 75 | Next Page >

- by Ben Shelock

- by ben martin

- by Ben

- by a different ben

- by Ben Shelock

- by sbenderli

- by Ben

- by Ben

- by Ben Sinclair

- by Ben

- by ben

- by ben

- by Ben Scheirman

- by Álvaro G. Vicario

- by Jonathan Kehayias

- by Adam Machanic

- by pinaldave

- by Paul Nielsen

- by Fatherjack

- by pinaldave

- by pablo.boixeda(at)oracle.com

- by Jonathan Kehayias

- by pinaldave

- by Peter Larsson

- by Pinal Dave

< Previous Page | 64 65 66 67 68 69 70 71 72 73 74 75 | Next Page >